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.
I have two tables like below:
Table A:
Table B:
I want to relate these two tables, but since their relationship is many to many, using Related function is not useful.
My objective results are:
when select ID=1:
Table A will be like:
I wish I could dynamically create this type of table A, having matched value from Table B based on the slicer ID.
What I did is to create a bridge table, Temp, using the expression of
Solved! Go to Solution.
Hi @Guli ,
You can set the many-to-many relationships status as inactive and create this measure to achieve it:
matchkey_with_TableB =
IF (
ISFILTERED ( 'Table B'[ID] ),
CALCULATE (
MAX ( 'Table B'[Filmtype] ),
USERELATIONSHIP ( 'Table A'[Film], 'Table B'[Filmtype] )
)
)
Attached sample file that hopes to help you: How to filter a table based on selected slicer result.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Guli ,
You can create this measure:
indicator =
IF(
SELECTEDVALUE('Table A'[Film]) = 'Table A'[matchkey_with_TableB],
1,BLANK()
)
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Guli ,
You can set the many-to-many relationships status as inactive and create this measure to achieve it:
matchkey_with_TableB =
IF (
ISFILTERED ( 'Table B'[ID] ),
CALCULATE (
MAX ( 'Table B'[Filmtype] ),
USERELATIONSHIP ( 'Table A'[Film], 'Table B'[Filmtype] )
)
)
Attached sample file that hopes to help you: How to filter a table based on selected slicer result.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@v-yingjl , the DAX is
Hi @Guli ,
You can create this measure:
indicator =
IF(
SELECTEDVALUE('Table A'[Film]) = 'Table A'[matchkey_with_TableB],
1,BLANK()
)
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much@Yingjie, this solution solved my problem!
@Guli - Put a bridge table in between them. Use the Bridge table in your slicer. A bridge table just has a distinct list of values from your tables. You can construct one like this:
Table = DISTINCT(UNION(SELECTCOLUMNS('Table1',"SomeName",[Column1]),SELECTCOLUMNS('Table2',"SomeName",[Column2])))
Make sure "SomeName" is the same for both.
Thank you@Greg_Deckler. The result is not quite what I want, it would work if I can figure out how to dynamically create a table(Or say, dynamically filter a table).
I want to filter table A as table B, based on the condition when Table A[column 1] is the result of a slicer(I will use column 1 as slicer). If I specifically point out
Table B=FILTER('Table A','Table A '[column 1]="1"), it will be a table that filtered from Table A with column 1=1,
what should I do to make this dynamic? I don't want to point out,' Table A '[column 1]=something every time, instead, using,'Table A '[column 1]=selectedvalue(Table[column 1]), but something this does not work. Do you have any suggestions?
Thank you!
@Guli - If you don't want to use a bridge table, and I'm not sure I understand why you are opposed to this, you could potentially use a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |