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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.