Forum Discussion
Need help in DAX for Table Filter
- 9 years ago
What dkay84_PowerBI suggested make sense but if you want to stick with your requirement, here is the solution which I think will work:
In your slicer table, add a calculated column to find out which column we want to filter, in case of Option 3 selected in filter, we are not filtering on any column
Which Column to Filter = if(Table2[Slicer]=1, "Column1", if(Table2[Slicer]=2, "Column2", ""))
Now add Measure in your slicer table, to check what value is selected on the slicer and then get the column value, in case option 3 or no value is selected then get Blank value otherwise get column name from previous calculated column we added to the slicer table
Which Slicer Option is Selected = IF(HASONEVALUE(Table2[Slicer]) = FALSE || VALUES(Table2[Slicer])=3, BLANK(), VALUES(Table2[Which Column to Filter]))
Now, go to your data table and add following measure, this is where you will put the logic (if statement):
What values to Filter = if([Which Slicer Option is Selected]=BLANK(),1, if([Which Slicer Option is Selected] = "Column1" && MAX(Table1[Column1])="C1", 1, if([Which Slicer Option is Selected]="Column2" && MAX(Table1[Column2])="D1",1, 2)))
Add this newly calculated measure from your data table to Visual Level Filters and under advance filter, select value is 1, as listed below.
Drop a table with your columns in it from data table, now when you select the value in the slicer, your table will filter based on selection.
I hope it will do the job, if you need pbix file, send me a private message with your email and I will send it over to you.
Cheers,
Parv
Here goes the sample data.
| Table1 | ||
| Col1 | Col2 | Col3 |
| C1 | D1 | E1 |
| C2 | D2 | E2 |
| C3 | D3 | E3 |
| Slicer |
| 1 |
| 2 |
| 3 |
Logic for Filtered Table which I need.
| Filtered Table |
| If Slicer is 1, then Filter Table1 with Col1 = C1 |
| If Slicer is 2, then Filter Table1 with Col2 = D1 |
| Else Return Full Table1 |
- priyamvid9 years agoRegular Visitor
Can u please help me with the DAX for Switch as you suggested.
Also, I have posted the sample data in the earlier poast and pbix is sensitive that is why I am not able to post here but I have provided the sample data.
I have charts created on the filtered table so when I select the Slicer, filtered table rows shud change dynamically i.e why I have used this logic. Any other way of achieving the same is also welcome.
Regards,
Priyam