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.
In my data model I have fact table, and in this table there is a column that can contain no, one or multiple values. If the column contains multiple values, the values are separated by ;
I want to filter the table on one or more single values.
Example: the column in my fact table looks like this:
A
null
A;B
B
A;B;C
C
A;C
When I filter on A, it should not only filter the rows that have A in the specified column, but also A;C, A;B and A;B;C
Best would be to create a relationship between the fact table and a table with all occuring single values, so in my report in can crossfilter, but I am not sure if that is possible.
I see similar problems on the forum, but none of them seems to fit perfectly. Can anyone help me with this?
Solved! Go to Solution.
Hi @TheoM,
Here are two solutions for your reference. But the first one only works when you select a single value in slicer each time.
Solution1.
Suppose you have a fact table and an extra table which lists all available slier items.
Create measures:
selected item = SELECTEDVALUE(Test2[Column2])
check =
IF (
NOT ( ISERROR ( FIND ( [selected item], SELECTEDVALUE ( Test1[Column1] ) ) ) ),
1,
0
)
Test3 = FILTER ( ADDCOLUMNS ( CROSSJOIN ( Test1, Test2 ), "Isexist", IF ( Test2[Column2] <> BLANK (), ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ), IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () ) ) ), [Isexist] = FALSE () )
Hi @TheoM,
Here are two solutions for your reference. But the first one only works when you select a single value in slicer each time.
Solution1.
Suppose you have a fact table and an extra table which lists all available slier items.
Create measures:
selected item = SELECTEDVALUE(Test2[Column2])
check =
IF (
NOT ( ISERROR ( FIND ( [selected item], SELECTEDVALUE ( Test1[Column1] ) ) ) ),
1,
0
)
Test3 = FILTER ( ADDCOLUMNS ( CROSSJOIN ( Test1, Test2 ), "Isexist", IF ( Test2[Column2] <> BLANK (), ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ), IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () ) ) ), [Isexist] = FALSE () )
Hello Yuliana,
Solution 1 works. Solution 2 seems to work too, but I have to do a bit more work on my data model (define relationships and add a few more measures). I am sure I can make that work.
Thank you!
Best regards,
Theo
Hey @TheoM,
What were the extra measures you needed to create to make this work?
I am having a hard time to do it.
Thnaks,
Sona
Well, your best case scenario isn't going to work as far as I can tell. But, measures based around something like the IN operator would probably be the way to go.
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.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |