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.
Hello all,
I have been working on a report where I have muliple tables linked to each other, and currently have an issue where I can not look up rows based on one value that can be present in multiple columns.
Example:
I have a table that shows data of a car dealership, where details, dates and times are logged.
Now, in this table there are two columns with peoples names - in this example a created by and a updated by, (which can be different names).
There is also a table which shows names of these people, and some more personal details.
My goal is to select a name in a table/list/slicer based on the People table and then every row in the Details table where this name is mentioned, (updated or created; does not matter) should show.
When I try to create a relationship, I can only select 1 column, so for example only the Create column.
Any ideas on how to achieve this?
Thanks very much in advance,
Kind regards,
Solved! Go to Solution.
This solution uses a clone of the Details table. Filtering is achieved via DAX instead of a relationship.
Create calculated table (no relationships):
DetailsVisual = Details
Create measure:
Visual Filter =
VAR vSelectedPeople =
VALUES ( People[Name] )
VAR vFilterDetails =
FILTER (
DetailsVisual,
DetailsVisual[Created By]
IN TREATAS ( vSelectedPeople, Details[Created By] )
|| DetailsVisual[Updated By] IN TREATAS ( vSelectedPeople, Details[Updated By] )
)
VAR vResult =
IF ( COUNTROWS ( vFilterDetails ) <> BLANK (), 1 )
RETURN
vResult
Create a slicer using the People table.
Create a table visual using fields from the DetailsVisual table, and add a visual filter using the measure above:
Result:
Proud to be a Super User!
This solution uses a clone of the Details table. Filtering is achieved via DAX instead of a relationship.
Create calculated table (no relationships):
DetailsVisual = Details
Create measure:
Visual Filter =
VAR vSelectedPeople =
VALUES ( People[Name] )
VAR vFilterDetails =
FILTER (
DetailsVisual,
DetailsVisual[Created By]
IN TREATAS ( vSelectedPeople, Details[Created By] )
|| DetailsVisual[Updated By] IN TREATAS ( vSelectedPeople, Details[Updated By] )
)
VAR vResult =
IF ( COUNTROWS ( vFilterDetails ) <> BLANK (), 1 )
RETURN
vResult
Create a slicer using the People table.
Create a table visual using fields from the DetailsVisual table, and add a visual filter using the measure above:
Result:
Proud to be a Super User!
This works perfectly, many thanks to you!
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |