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.
Hi,
I'm looking for ideas on how to connect two tables without a strong relation between fields. The idea is that once I select a value from Table A, I would use the column "class" to filter Table B when its lines would contain the value from TableA.class. Tables are like this (simplified):
Table A
Location Class
============== =====
Location 1 C
Location 2 C
Location 3 B
Location 4 A
Location 5 B
...
Table B
Question PossibleClasses
============== ===============
Question 1 /A////
Question 2 /A///D/
Question 3 /A/B/C/D/
Question 4 /A//C//
Question 5 /A/B///
So when I select a "Location" from Table A, using its "class" column I'd like to filter the questions that the content is present on column "PossibleClasses" content, something like:
"Filter 'Table B' where 'Table B'.PossibleClasses contains 'Table A'.class"
So if I choose Location 2 (class C) Power BI would filter Table B to display only questions 3 and 5.
Any ideas? Thanks in advance!
Solved! Go to Solution.
@OCJ create this measure:
Show Line =
VAR _classes = DISTINCT('Table A'[Class])
VAR _posiible_classes = DISTINCT('Table B'[PossibleClasses])
VAR _cross = CROSSJOIN(_classes, _posiible_classes)
VAR _filtered_table =
FILTER(_cross, CONTAINSSTRING([PossibleClasses], [Class]))
VAR _result =
IF ( NOT ISEMPTY(_filtered_table), 1, 0)
RETURN
_result
Add it to the visual level filter of Table B and set it to 1:
Now, if I choose something from Table A it will do what you want:
@OCJ create this measure:
Show Line =
VAR _classes = DISTINCT('Table A'[Class])
VAR _posiible_classes = DISTINCT('Table B'[PossibleClasses])
VAR _cross = CROSSJOIN(_classes, _posiible_classes)
VAR _filtered_table =
FILTER(_cross, CONTAINSSTRING([PossibleClasses], [Class]))
VAR _result =
IF ( NOT ISEMPTY(_filtered_table), 1, 0)
RETURN
_result
Add it to the visual level filter of Table B and set it to 1:
Now, if I choose something from Table A it will do what you want:
Works like a charm! Thanks a million!
@OCJ my pleasure 🙂
Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |