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 Everyone,
I'm trying to create a dashboard to show if there has been coverage, or no coverage for a specific car. This includes comparing the two tables to see if both subcategories exist. If they match (e.g Toyota is in both tables), then its considered "Coverage", if the relationship doesnt exist (e.g Audi in the unique table but not the car history table), then its considered "No Coverage". The issue i'm having is that is appears when i try and filter on year, the values are not updating as i expect. The two tables I am using look like this:
Unique Table (subcategory primary key)
Business Unit | Category | Subcategory |
Cars | a | Ford |
Cars | b | Chevy |
Cars | c | Toyota |
Cars | c | Infinity |
Cars | d | Acura |
Cars | e | Audi |
Cars | f | BMW |
Cars | g | Buick |
Car History
Name | Year | Subcategory |
Shelly | 2021 | Infinity |
Carol | 2022 | Chevy |
Mumford | 2023 | Toyota |
The Unique table has a one to many relationship with the Car History table.
I would like to be able to filter on "year" using a slicer, to get the following results (the actual dashboard is displayed as a stacked bar chart with coverage and no coverage as the legend):
All years: | ||
BU Name | Coverage | No Coverage |
Cars | 3 | 5 |
Filter to exclude year 2021 | ||
BU Name | Coverage | No Coverage |
Cars | 2 | 6 |
Filter to exclude year 2021 & 2022 | ||
BU Name | Coverage | No Coverage |
Cars | 1 | 7 |
The first step i took in this process was to determine if a relationship existed. I created a 'related value' column in the car history table so i can do a lookupvalue function in the unique table to show if the relationship existed.
Car History:
Related Value = if(Related('Unique Table'[subcategory])='Car History'[subcategory],1,0)
Unique Table lookup:
Coverage? = if(lookupvalue('Car History'[Related Value],'Car History'[subcategory],'Unique Table'[subcategory]),"Coverage","No Coverage")
From there, I created another measure called 'Coverage Count':
Coverage Count = DistinctCount('Unique Table'[subcategory])
When plotting this on a stacked bar chart, with 'Coverage?' as the legend, 'Coverage Count' as the x-axis, and 'Business Unit' as the y-axis, it works fine. However, when I try and filter using the 'Year' column in the 'Car History' table, it completely removes the "no coverage" portion, and only shows the coverage portion (which updates correctly).
Can someone please assist? Your help is very much appreciated!
Thank You
Solved! Go to Solution.
Hi, @Specialist707
Your [Coverage?] is a calculated column, so the column's value is static and won't be affected by the slicer "Year". In addition, Slicer 'Year' may filter out part of the data in 'Unique Table'.
You can try my steps below.
1.add a seperate legend table that has no relationship with other tables
2.add measure formula like:
Coverage? =
IF (
MAX ( 'Unique Table'[Subcategory] ) IN VALUES ( 'Car History'[Subcategory] ),
"Coverage",
"No Coverage"
)
Subcategory count =
CALCULATE (
DISTINCTCOUNT ( 'Unique Table'[Subcategory] ),
FILTER ( 'Unique Table', [Coverage?] = MAX ( Legend[Coverage?] ) )
)
Best Regards,
Community Support Team _ Eason
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, @Specialist707
Your [Coverage?] is a calculated column, so the column's value is static and won't be affected by the slicer "Year". In addition, Slicer 'Year' may filter out part of the data in 'Unique Table'.
You can try my steps below.
1.add a seperate legend table that has no relationship with other tables
2.add measure formula like:
Coverage? =
IF (
MAX ( 'Unique Table'[Subcategory] ) IN VALUES ( 'Car History'[Subcategory] ),
"Coverage",
"No Coverage"
)
Subcategory count =
CALCULATE (
DISTINCTCOUNT ( 'Unique Table'[Subcategory] ),
FILTER ( 'Unique Table', [Coverage?] = MAX ( Legend[Coverage?] ) )
)
Best Regards,
Community Support Team _ Eason
Thanks - that is helpful. Is it possible to be able to filter on the bar chart so that if I select the 2 Coverage, then it filters the table on the top left to only show the records with coverage? How would I go about doing that?
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |