Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WeeDiugster
Regular Visitor

Using a Slicer to Set Filter Values on Other Columns

Hi I'm just starting out with DAX so apologies if this has been asked before.

 

I have a table with 20+ flags.  So things like IsCustomerRegistered, IsPensioner etc.  Each flag has a 1 for YES and 0 for No.  I currently have slicers for each of the 20 flags which works fine.

 

What I would like to do is have a slicer which offers the names of the 20 flags and when one is selected, the value of that flag is set to 1 and so the table filtered to see for example all customers who are registered (IsCustomerFlag =1).

 

I have created a table with the names of the 20 flags and created a slicer and arranged it horiziontally so it looks like a series of buttons.  I have got as far a being able to use SELECTEDVALUE to determine what slicer selection has been made but I'm now struggling to make use of this and set the filter value on the respective flag.

 

To take it one step further, ideally I would like to be able to multi select on the slicer and so set multiple flags to 1 at the same time.

 

Is this possible?  Thanks in advance.

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

Hi @WeeDiugster ,

 

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

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

Hi Neeko and thanks for replying.

 

OK I have a fact table that looks like this:

 

IDNameIsRegisteredIsMemberIsParentIsUnemployedIsPensioner
1Henry Smith10100
2Maisie Jones01001
3Jo Peg11000

 

I then have another table that contains a list like this:

 

FlagOrder
Is Registered1
Is a Member2
Is a Parent3
Is Unemployed4
Is a Pensioner5

 

There is no relationship between the 2 tables in the model.  I have created a slicer using the the second table and changed the settings so that the values are displayed horizontally and look like this:

 

Is RegisteredIs a MemberIs a ParentIs UnemployedIs s Pensioner

 

When I choose any of these options from the slicer, I would like a corresponding filter of 1 to be set on the appropriate flag in the first table.  So if I selected Is a Member and IsPensioner, then my table would show this:

 

IDNameIsRegisteredIsMemberIsParentIsUnemployedIsPensioner
2Maisie Jones01001

 

I.e. IsMember = 1 and IsPensioner = 1 has been applied as a filter.

 

I know I could probably do this with bookmarks and buttons, but my real table has approximately 40 of these flags so it could be messy!

 

Thank you for taking the time to look at this.

 

Hi @WeeDiugster ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours. Please note that the flag name in the fact table you give and the other table are not exactly the same, please name it uniformly.

vtangjiemsft_0-1676360829956.png

(2)Click "transform data", go to the power query editor, select the [ID], [Name] columns, click "Transpose other columns", and then click "Close and apply".

vtangjiemsft_1-1676361118245.png

(3) We can create a measure. 

Measure = 
var _a = SELECTCOLUMNS('Table',"Flag",[Flag])
var _b = ADDCOLUMNS('Fact Table',"Flag",IF([Value]=1&&[Attribute] in _a,1,0))
return IF(OR(ISFILTERED('Table'[Flag])&&SUMX(_b,[Flag])=COUNTROWS(_a),NOT(ISFILTERED('Table'[Flag]))),1,0)

Then place [Measure] on the visual to filter.

vtangjiemsft_2-1676361323905.png

(4) Then the result is as follows.

vtangjiemsft_3-1676361363418.png

Best Regards,

Neeko Tang

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

Thank you so much. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.