slicer no selection

[Anonymous]

Hello, being new in power BI, I have a lot of beginner questions!

I want to count and display the disctint values of a specific column, for that I created a measure using DISTINCTCOUNT function. 

In addition, I have a slicer on a different column and people can select all or only 1 of the options, there is also the possibility to select nothing.

When this case happens, I would like that the count is 0 but indeed it is not, it counts me still like having the full table.

Did I miss something about slicer/filter logic? i guess already if the outcome would be blank() then I could use an IF statement with isblank() but here it is even not the case.

Thanks for your help,

[v-danhe-msft]

Hi @Anonymous,

Could you please offer me some sample data to have a test and post your desired result?

Regards,

Daniel He

[Anonymous]

Thanks for offering your help,

Attached is my table and my display (with my slicer and my attempts to count)

Indeed I did two attempts of measures

countProd = DISTINCTCOUNT(Sheet7[Product])

countProd with is filtered = if(ISFILTERED(Sheet7[CTRY]);DISTINCTCOUNT(Sheet7[Product]);0)

for countProd, my problem is that the results when select all is checked and when there is nothing checked is the same (4) whereas it should be blank and after I can put a if condition for this blank

for countProd with is filtered, it solved the problem when nothing is selected but now when > 1 thing is selected is filtered = false and then it put 0 while it should be 4.

Thanks a lot!

[v-danhe-msft]

Hi @Anonymous,

I have modified your formula, you could refer to it:

countProd = IF(ISBLANK(SELECTEDVALUE(Sheet7[Product])),0,DISTINCTCOUNT(Sheet7[Product]))

countProd with is filtered = if(ISFILTERED(Sheet7[CTRY]),0,DISTINCTCOUNT(Sheet7[Product]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft

Thanks, unfortunately I could not open your pbix cause my power bi desktop session could not open it (maybe too old?).

Based on your two new formulas for the measures:

- countProd still does not work but now it works when nothing selected but still when selected >1, it shows 0, so i dont see how come it enters into the isblank() except if selectedvalue is only 1 and exactly 1 value...

- countProd with is filtered works now IF i remove the select all option.

Any clues about the first countProd? can it come from my power bi desktop version?

Thanks again for your help

[Anonymous]

Hi @v-danhe-msft 

I manage to open your power bi file, and it results that the difference between your solution and mine is that I need that the user can select ONE or multiple answers (countries in my case or products in the case you reproduce). so when I change the slicer into off "single select" then I still don't get the counts that I want using either the first count or the second one. WHen nothing is selected there is always the total number that appears and not 0. 

I would like to have an IF condition about if nothing is selected but apparently isblank(isselected()) is not the right one, cause isselected works for a single selection.

Thanks for your further help!

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

I have modified my formula with countProd, you could refer to it:

countProd = IF(ISFILTERED(Sheet7[Product]),DISTINCTCOUNT(Sheet7[Product]),IF(ISBLANK(SELECTEDVALUE(Sheet7[Product])),0))

Result:

Hope it could help you.

Regards,

Daniel He

[Anonymous]

It works thanks a lot!

Would you tell me which power bi version do you have?

Because your pbix file can't be opened using my version,

Thanks again!

Regards,

[v-danhe-msft]

Hi @Anonymous,

Here is my version: 2.62.5222.601 64-bit (September 2018)

Hope it could help you.

Regards,

Daniel He