Forum Discussion
Need help in DAX for Table Filter
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
Yes No relation between slicer and Transformed Data table.
So your end goal is to return a filtered table based on the slicer selection? Or do you want to perform a calculation/aggregation of the values in the table after filtering is applied?
Why don't you just add a column to your data table with If Then logic to put "Slicer 1" if the value in Col1 = "C1", etc?
Then you just add this calc column as the field in the slicer, and when a user selects a slicer value, the resulting table will be filtered to return only the rows that matched the condition of the logic you programed.
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
Seems like he is looking for filtered table.