Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Rows need to filter
A |
A,B,C |
B |
D |
B,C,D |
A |
B |
If use put above column to filter, it will list(A; A,B,C; B; D; B,C,D; )
What I want to see in fitler is
expected filter
A |
B |
C |
D |
When I select A, should list, A; A,B,C; A
What I do currently is to create a fitler table.
remove value duplicate -> copy value -> split value to new lines
value value filter
A | A |
A,B,C | A |
A,B,C | B |
A,B,C | C |
B | B |
D | D |
B,C,D | B |
B,C,D | C |
B,C,D | D |
Question is: I think my method is not good.. Is there any other better and easier way to hanle this?
Solved! Go to Solution.
Hi @benson
When you say filtering A, you are talking about using Slicer to filter, and you need a DAX measure to display the reulst in a Card visual? If yes, two disconnected tables, one is filterTable in Slicer, one is rowTable. If you need to keep the original order, add an index column in Power Query
test =
VAR CurFilter = SELECTEDVALUE(filterTable[Column1])
VAR T1=
ADDCOLUMNS(rowTable,"check",IF(SEARCH(CurFilter,rowTable[Column],1,-1)=-1,0,1))
RETURN
CONCATENATEX(FILTER(T1,[check]=1),[Column],";")
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @benson ,
Go to query editor>duplicate the column>split the new column by delimiter(,):
And you will see:
No need to create a new table or dax expression.
Check my sample .pbix file attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
thank you so much for your answer. i have already tried your way, but it only works for table visualization. what if i want to apply it to pie chart or bar chart? is there any other way i can do?
This is what I'm doing now. but due to I have many other column in this table. So split to new row will impact other data. So I have to create new table.
Hi @benson ,
Thus create a slicer table is much better,have you checked the solutions above,are they what you need?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @benson
When you say filtering A, you are talking about using Slicer to filter, and you need a DAX measure to display the reulst in a Card visual? If yes, two disconnected tables, one is filterTable in Slicer, one is rowTable. If you need to keep the original order, add an index column in Power Query
test =
VAR CurFilter = SELECTEDVALUE(filterTable[Column1])
VAR T1=
ADDCOLUMNS(rowTable,"check",IF(SEARCH(CurFilter,rowTable[Column],1,-1)=-1,0,1))
RETURN
CONCATENATEX(FILTER(T1,[check]=1),[Column],";")
This also need create another table, is there any method that not use new table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.