Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
shane7mcdonald
Frequent Visitor

Best way to join tables to get missing values?

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 IDQual Name
1

Qual A

2Qual B
3Qual C
4Qual D

 

Fact Table...

NameQual IDQual Name

Bill

1Qual A
Bill2Qual B
Bill3Qual C
Jane3Qual C
Jane4Qual D
Peter2Qual B
Peter3Qual C
Peter4Qual D

 

User selects Qual A in Dim slicer.

 

Result should be...

 

NameQual IDQual NameStatus
Bill 1Qual AYes
Jane1Qual ANo
Peter1Qual ANo

 

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

 

Thanks!

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1718003855078.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1718003855078.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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...

 

shane7mcdonald_1-1718014450704.png

 

Cheers!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!