Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a table in power bi and I am trying to filter it using some column value.
I want to make the filter as dynamic as possible which means that I need a dax which filters the table using different columns based on the value of slicer (disconnected).
Please help me .
I have already written a DAX for FIltered Table.
FilteredTransformedData =
Var IndSlicer = IF(COUNTROWS(VALUES(Table5[Slicer]))=1, VALUES(Table5[Slicer]), TRUE())
RETURN FILTER(All('Transformed Data'), IF(Exact(IndSlicer,"Top10"),'Transformed Data'[group/Top10],IF(IndSlicer<>"",'Transformed Data'[group/Bottom10],true)))
Here , IndSlicer is always giving blank rows despite I am selecting the Slicer value.
I am not sure what is wrong here. Could anyone help me in this .
Regards,
Priyam
Solved! Go to Solution.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I tried using HASONEVALUE also and it is producing the same result.
Sure, I can send.
Do you have skype?
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 |
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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |