Create a slicer with binary information from multiple columns

[ljx0648]

Hi guys,

I have a customer count table as per below:

Customer Count = the count of customer with the attribute on the right.

Use 1st row for instance, there is 1 customer with Mutual Fund ONLY (Mutual = Y, GIC & CASH = N).

I am looking for a way to create a slicer that looks like this 

With Mutual Fund

With GIC

With Cash

Thus, if I select With Mutual Fund , I will get customer count = 1+1+2+3+4+5 = 16 (all the rows with mutual fund = Y)

Lastly, if I multi-select With Mutual Fund & With GIC, I will get customer count = 1+2+3+4+5 = 15 (all the rows with mutual fund = Y AND with GIC = Y).

Any idea is much appreciated!

Thank you

[Ritaf1983 · Accepted solution]

Hi @ljx0648 
To get the desired result :
You need to unpivot the table from PQ :

At this stage you can create simple dax measure :

Count_Y = CALCULATE(COUNTROWS('Table'),'Table'[Value]= "Y")

Result :

Pbix is attached

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

[ljx0648]

Hi Ritaf,

Thank you for your idea. Your idea works as listed!

[Ritaf1983]

Happy to help 🙂