Forum Discussion
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
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:
ID Name IsRegistered IsMember IsParent IsUnemployed IsPensioner 1 Henry Smith 1 0 1 0 0 2 Maisie Jones 0 1 0 0 1 3 Jo Peg 1 1 0 0 0 I then have another table that contains a list like this:
Flag Order Is Registered 1 Is a Member 2 Is a Parent 3 Is Unemployed 4 Is a Pensioner 5 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 Registered Is a Member Is a Parent Is Unemployed Is 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:
ID Name IsRegistered IsMember IsParent IsUnemployed IsPensioner 2 Maisie Jones 0 1 0 0 1 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.
(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".
(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.
(4) Then the result is as follows.
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.
