DAX formula count single value in D365 multiselect field

[Pwrbi_usr]

Hello, 

In a Power Bi report, connected to Dynamics 365 environment by direct query, i need to count how many times a single value (1) of a multiselect field appears in the column.

Examining the input data, I found that the values have the following format:

I tried with the following expression, but it counts every single row:

Single option = CALCULATE(COUNTROWS(campaign), NOT(ISBLANK(FIND("1", campaign[Multiselect Field]))))

I also tried with this other expression, and count only rows that has the single value "1":

Single option = CALCULATE(COUNTROWS(campaign),CONTAINSSTRING("1", campaign[Destinatari Campagna]))

Can someone help me?

[v-jianpeng-msft]

Hi, @Pwrbi_usr 

Based on your description, I've created the following sample data:

Using the Find function is the right choice. First, I created a dummy table in my measure as follows:

Second, I use the countax function to count the column columns. The final measure expression is as follows:

Has1value = 
VAR _table = SUMMARIZE('Table','Table'[Column1],"a",FIND(1,'Table'[Column1],1,0))
RETURN COUNTAX(FILTER(_table,[a] <>0),'Table'[Column1])

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pwrbi_usr]

I tried your  DAX expression, but if there is the same value in multiple rows, only one is considered:

While in input I've 10 rows:

[v-jianpeng-msft · Accepted solution]

Hi, @Pwrbi_usr 

Thank you very much for your reply. Since the summary will ignore the rows that have the same value. I modified this DAX expression:

This way rows with the same value will not be ignored, and the final DAX expression will look like this:

Has1value = 
VAR _table = ADDCOLUMNS(CALCULATETABLE('Table'),"a",FIND(1,'Table'[Column1],1,0))
RETURN COUNTAX(FILTER(_table,[a] <>0),'Table'[Column1])

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.