Slicer_Add Multiple Columns in a Slicer that are not a Hierarchy

[PowerBI_User__]

Hello,

I'd like to add a slicer to filter on the account ID by Program Type. The slicer should have a dropdown list including:

Program A

Program B

What's the best way to buid that?

Account ID	Program A	Program B
1702892911	x
1702853685	x	x
1702848829

[Alex_Sawdo · Accepted solution]

In a case like this, it would probably be easier to structure this in Power Query over trying to write a complex DAX function. What you can do is reference the initial query and unpivot your new table so program A and B are in one column, differentiated by an attribute. Then, you can join your first table with this new table and add a slicer based on the new table. Then in your measure, you can filter the new table for values that aren't blank to get an accurate count. 

Measure =

CALCULATE(

    COUNT(

        'Fact'[Account ID]

    ),

    FILTER(

        'Fact',

        'Fact'[Value] <> BLANK()

    )

)

[Alex_Sawdo · Accepted solution]

In a case like this, it would probably be easier to structure this in Power Query over trying to write a complex DAX function. What you can do is reference the initial query and unpivot your new table so program A and B are in one column, differentiated by an attribute. Then, you can join your first table with this new table and add a slicer based on the new table. Then in your measure, you can filter the new table for values that aren't blank to get an accurate count. 

Measure =

CALCULATE(

    COUNT(

        'Fact'[Account ID]

    ),

    FILTER(

        'Fact',

        'Fact'[Value] <> BLANK()

    )

)

[PowerBI_User__]

Many Thanks Alex!!

Yes, creating a new table and unpivoting the columns wokred! And I connected the 2 tables with a bi-directional filter!

Thanks a lot for your response!

[Wilson_]

Hello,

I just helped a different user with the same or similar issue this week here. Please let me know if that works for you. Happy to help if you read through there and it's not what you were looking for. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[PowerBI_User__]

Hi Wilson,

• Thanks a lot for your reply. I read through the other solution but I am not following through 😕 I think my case is simpler. So my dataset is connected to the "account ID". And I'd like to be able to filter the views on the account IDs based on the "Program Type". i.e Program A contains a set of these account IDs, and Program B contains another set of these account IDs. 

[Wilson_]

I see, maybe I've misunderstood your data model or your request. I assumed the table in your original post was the table in your pbix. Is it?

I'm also honestly not quite following how you're describing what you're looking for. Can you provide a visual of what you would like to see in a final result?

[PowerBI_User__]

Hi Wilson,

Yes, please find attached a sample of the table I am loading to powerbi. The final result I am looking for, is having one slicer where I can filter between Program A "TREX" and Program B"Delphix".  The dataset is connected to the account ID, so when I filter on those two columns, all other views will get filtered on the accounts under each program.Sample Data 

[Wilson_ · Accepted solution]

Hello,

So this is a very similar scenario to the other post I linked you to, except you can skip the split by delimiter step. You just need the unpivot step. Once you unpivot the Program A and Program B columns, then delete Values column with all x's, you'll see the below.

Then if your table is joined if joined to other tables by the Account ID, you should be able to use the Attribute field (you can rename it in the Power Query steps to "Program" or something) as your slicer.

Let me know if that's what you're looking for. Hopefully it makes sense. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[PowerBI_User__]

Hi Wilson,

yes now it's clear 🙂 so I have created a new table and unpivoted the columns, because when I unpivoted the columns in the same table I get duplicate account IDs, which is messing with the inner joins I have with all the other tables. I connected the new table with the old with 1:1 relationship of the account ID. The slicer is based on the new "attribute" colun but it's still not working.. I guess I still need one more step?

[Wilson_]

Hello,

Hard to say without knowing anything about the rest of your data model. It might be as simple as turning bi-directional filtering on between the original table you shared and the unpivoted table you created and linked to. It might also not be, again, based on the rest of your data model.

Any chance you can share your pbix (or at least a smaller sample version)?

[PowerBI_User__]

Hi Wilson

Thanks a lot mate! You're brilliant 🙂 Yes turning on the bi-directional filter between both tables made the slicer work!

Many Thanks for your support and prompt response!!

[Wilson_]

Awesome, you're welcome. Glad that worked for you.

It seems without the bi-direction filtering, filtering the new unpivoted table was not propogating back to the original table, which was needed to filter through the rest of your tables. 🙂