Best way to join tables to get missing values?

[shane7mcdonald]

Hi guru's, just wondering the best way to get the below result. Basically I have a Dim table with a list of qualifications (40,000 rows) and I have a Fact table with a list of qualifications people currently have (~2 million rows).

I want a table that returns the qualifications they HAVE and DON'T HAVE based on the qualification filter they select from the Dim table.

See example below.

Dim table...

Qual ID	Qual Name
1	Qual A
2	Qual B
3	Qual C
4	Qual D

Fact Table...

Name	Qual ID	Qual Name
Bill	1	Qual A
Bill	2	Qual B
Bill	3	Qual C
Jane	3	Qual C
Jane	4	Qual D
Peter	2	Qual B
Peter	3	Qual C
Peter	4	Qual D

User selects Qual A in Dim slicer.

Result should be...

Name	Qual ID	Qual Name	Status
Bill	1	Qual A	Yes
Jane	1	Qual A	No
Peter	1	Qual A	No

I've been spending too much time in Power Apps and have now forgot all my Power Bi chops...🤣

Thanks!

[ThxAlot · Accepted solution]

Simple enough,

[shane7mcdonald]

Ha ha, you're right @ThxAlot that was simple! I was way overcomplicating things...

Another quick one, any ideas why with that measure if I try and filter by another dim table (organisation heirachy) it 'breaks' the filtering? I assume i need something extra in the measure? I dont quite understand whats happening in the backend here...

Cheers!