Forum Discussion
Using a Slicer to Set Filter Values on Other Columns
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.
Thank you so much.