Pass multiple values to parameters in DAX from paginated report

 Hi, 

Have u encountered how to pass multiple values in DAX filter from paginated report?

If yes, then follow the steps for implementation :)

First set parameter is set to accept multiple values from user.

Then in Dataset properties in Parameters tab, set the value of the parameter with following:

=Join(Parameters!myParameter.Value, "|")

Then inside you Query Designer, this is how you will define the filter

VAR __DS0FilterTable =  FILTER(

KEEPFILTERS(VALUES('someTable'[someCol])),               PATHCONTAINS(@myParameter,'someTable'[someCol])

)

HTH

Pass parameters in Powerbi Paginated reports to SQL Server

To pass parameters to SQL Server from Powerbi Paginated reports (Powerbi Report Builder), we will declare parameters with some name and then use this parameter using escape character `@` in the query builder.

Example:

HTH

Pass parameters in Powerbi Paginated reports to Azure databricks using Simba ODBC

To pass parameters in Azure Databricks from Powerbi Paginated reports (Powerbi Report Builder), we will use ? as parameters name (yes you can define multiple parameters and all with ?) and then in same order parameters are applied to query.

Example:

HTH

Publish Powerbi Streaming Dataset via API

Streaming dataset can be challenging if not understand right. There are limitations when created from Powerbi workspace i.e. it will always create streaming dataset with FIFO supporting 200,000 rows.

So to enable streaming dataset to support 5 million rows, it would require API calls to create dataset.

Below example using postman posting calls.

1. First it require Security Token to be sent as part of Post request.
2. Then inside the Body of Post request, make the body type JSON and define your dataset (see sample dataset). 
3. Third, we need to make sure to set defaultRetentionPolicy = None passed as request parameter. 

Here is Http post request looks like:

https://api.powerbi.com/v1.0/myorg/groups/<PUT_WORKSPACE_ID_HERE>/datasets?defaultRetentionPolicy=None

Sample dataset in the body of post request:

{
  "name": "Sample_Streaming_Dataset_5m",
  "defaultMode": "PushStreaming",
  "tables": [
    {
      "name": "RealtimeDT5m",
      "columns": [
        {
          "name": "Sales",
          "dataType": "Double"
        },
        {
          "name": "Credits",
          "dataType": "Double"
        },
        {
          "name": "StoreId",
          "dataType": "string"
        },
        {
          "name": "BusinessDay",
          "dataType": "DateTime"
        }
      ]
    }
  ]
}

Posting will create a Streaming Dataset.

HTH

Databricks Notebook Reference Paths

To reference a notebook from another notebook, you need to give relative path from your current notebook. 

Example: If your working notebook are structured as below:

1. /Users/johnDoe/Processing/ProcessFile
2. /Users/johnDoe/Processing/BinaryFiles/ProcessBinaryFiles

and folder where the referenced notebook is placed:

     /Users/johnDoe/Referenced/myReferencedNotebook

then to reference the notebook from ProcessFile notebook would be:

%run ../Referenced/myReferencedNotebook

and to reference the notebook from ProcessBinaryFiles notebook would be:

%run ../../Referenced/myReferencedNotebook

HTH

kill all chrome processes powershell

Powershell command:

> kill -name chrome

Enjoy!

Remove seconds pyspark sql

 To remove seconds in pyspark sql in notebook:

SELECT 

        date_trunc('day', current_timestamp) as DateTimeWithZeroHoursMinutes  

        date_trunc('minute', current_timestamp) as DateTimeWithZeroSeconds,

        date_trunc('second', current_timestamp) as DateTimeWithZeroMilliseconds,

        date_format(current_timestamp, "yyyy-MM-dd HH:mm") as DateWithNoSeconds

Access file/folder path in databricks

In databricks notebook, following are the way 2 ways to access the folder (example folder name: mnt):

1. If you are reading from DBFS with pyspark, then you start your filename like this ‘/mnt/….’
2. If you are reading form DBFS with regular python, you start your filename like this ‘/dbfs/mnt/…’

HTH

Setup Single Node Job Cluster from Datafactory

 To Setup Cluster from datafactory job - 

1. First we need to make the Workers to 0 - Yes set it to 0. See image below:
   
   
2. Then under Addition Cluster Settings, look for Cluster Spark conf and set following name/values.
   
   "spark.databricks.cluster.profile": "singleNode"
   "spark.master": "local[*, 4]" 
   
   
   
   
   
3. Last we need to set Tag under Cluster custom tags:
   
   
   "ResourceClass": "SingleNode" 
   
   
   

   
   
   

That's it!!!

HTH

org.apache.spark.sql.AnalysisException: cannot resolve 'explode(`mycol`)' due to data type mismatch: input to function explode should be array or map type, not struct line 1 pos 0;

Hello,

If you exploding and got above error then it means you dont need to explode it as it is not type array or map.

You can simply access values as:

df_expanded = df.withColumn("prop1", expr("mycol.prop1"))

HTH

Is HBL and Bank AL Habib same???

 Hello -

The answer is NO.

These are 2 different banks - 

HBL Iban starts as: PK HABB

Bank AL Habib Iban start as: PK BAHL

HTH

Power BI Dynamic Row Level Security (RLS)

If you were like me trying to implement RLS using fields other than email, then you have come to right place.

RLS will automatically replace the value in filter for you.

Simply use the UserPrincipalName() or UserName() function on the column you want to identify users. It can be any string that can identify user. 

Example: Email Id, Guid, Name etc.

Power BI automatically replace the value in the function based on the value that will be supplied to apply the security in the report.

HTH.

Get Row Count in Powerbi Report (paginated report)

 Hi, How to get total number of rows in your report?

1. Make sure you select a field which is NOT Nullable.
2. Then insert a Textbox in the report
3. Write Expression to display data in the expressions for Textbox.
4. Expression:  =Count(Fields!EmployeeName.Value, "DataSet1")

HTH

Azure BlobTrigger Function (v3) trigger twice

Hello,

Recently we came across a scenario where when the file was being uploaded to Blob via MS Flow, the function trigger (BlobTrigger) would trigger twice which will make the file to run thru function twice. 

Very frustrating!

After couple days of research, found that MS Flow has a setting that lets you upload of file in chunks. 

Turn it off and whola, problem fixed.

Here is screenshot of setting:

Purge Azure Durable Functions Orchestration History

In this post, I will show how can you clear the orchestration history which is being stored while the function is being executed.

This will help you in the development / debugging process because you dont want to have those activities started while you working on your current problem.

Before you do that, you need to do followings:

1. Install Function CLI tools (see link below)
2. Check what Task Hub Name your function is using.

Run the command from Command Prompt (Administrator mode) by setting source as your project folder where host.json files live, usually root folder of the project.

The command is :
func  durable delete-task-hub  --task-hub-name  myfunctionDemoHub

By default, the function uses "TestHubName" if not one set. 

To set your own "Hub Name", you would need to edit host.json as below:

{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "hubName": "myfunctionDemoHub"
    }
  }
}

To see the hub used by the function, you can that on console:

This link has usage has many commands that can be useful.

HTH

How to see MSMQ in Windows10?

If you have MSMQ component installed, then to go to MSMQ, you would do following:

1. Click Start 
2. Start typing Computer Management
3. This will open a MMC window 
4. Under Services and Applications -> Message Queuing

Cannot load script for execution to new SQL Server version

To fix above error, make sure the SSIS project is referenced to right SQL Server version.

To do so:

1. Right click on Integration Project
2. Click Properties
3. Pick your sql version (see image below):

Merge join unpredictable behaviors

Today while on SSIS package, found very strange behavior where I will keep seeing rows coming for update but nothing would update because there was nothing to update.

Also would see rows that exist in destination but Merge join will bring them for insert. Strange!

After hours of research, found if you have "Sort" defined in one source in SSIS and order by defined in other source inside SQL Server then you will see all kind of unexpected behaviors.

Sort operation in SSIS is caSE Sensitive and it will NOT work right if there is data source that already is sorted in sql server.

So in short:
1. Dont mix sources with "Sort" in SSIS and with "order by" defined in sql server.
2. Sort operation inside ssis is caSE sensitive.
3. Keep one of the order by kinds. Either all from sql server or just defined inside SSIS.


HTH

Testing SSIS connectivity hangs...

Hi,

If you are experiencing "Testing Connectivity..." hang issue where the VS is just waiting then it is OK to kill the instance and review the packages using text editor.

In my case I had Connect Timeout = 0 and if server names were not present, the VS will never stop searching for it.

I had to open SSIS package in Textpad, search for Connect Timeout setting and change it 30(seconds) from 0.

OR

Other option would be to Work Offline.
But this option will only be set once the package open fully after Testing connectivity is done.

HTH.

Visual Studio 2019 - SSIS Project InCompatible

Hello, You will be surprised that Visual Studio itself will give you hint to disable SSIS extension.

and once you disable this extension, your SSIS project is not compatible with VS 2019 any more.
To fix this, you need to do re-enable it.

Choose Extensions > Manage Extensions. Go to Installed enable it there.

HTH