Forum Discussion
Calculated Column & Filter Context Issue between two related tables
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
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
3 Replies
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 _ EasonThanks - 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?
