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:
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?
Go to Solution.
Here are two solutions for your reference. But the first one only works when you select a single value in slicer each time.
Suppose you have a fact table and an extra table which lists all available slier items.
selected item = SELECTEDVALUE(Test2[Column2])check =IF ( NOT ( ISERROR ( FIND ( [selected item], SELECTEDVALUE ( Test1[Column1] ) ) ) ), 1, 0)
CROSSJOIN ( Test1, Test2 ),
"Isexist", IF (
Test2[Column2] <> BLANK (),
ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ),
IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () )
[Isexist] = FALSE ()
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.
Proud to be a Datanaut!
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.
What were the extra measures you needed to create to make this work?
I am having a hard time to do it.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.