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
I tried using HASONEVALUE also and it is producing the same result.
- dkay84_PowerBI9 years agoMicrosoft EmployeeCan you share your pbix or some sample data?
- priyamvid9 years agoRegular Visitor
Sure, I can send.
Do you have skype?
- priyamvid9 years agoRegular Visitor
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