Calculated Column & Filter Context Issue between two related tables

[Specialist707]

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

[CNENFRNL]

[v-easonf-msft · Accepted 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

[Specialist707]

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?