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
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
Hi priyamvid
Just reading this thread, so you are saying there is no relation between these tables, correct?
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
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?
Seems like he is looking for filtered table.
