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
mattao44
Frequent Visitor

Multiple IDs to one fact table

I have the following example where I have in the Code mapping table two IDs which one relates to the current year ID and the second to the prior year ID. It is important to map them against eachother so that we can do calculations such as current year net revenue vs. prior year net revenue. 

 

I am struggling to figure out the best way to structure the data model vs. how much to takle with DAX. Any ideas on the best way to model it?

 

Mapping Example.png

1 ACCEPTED SOLUTION

Hello @mattao44 

Attached is the pbix, with small changes in the mapping table, and added measures to the fact table, and see the table : Hope this is what was intented. If you don't want to change the mapping table structure it can be achieved using different method. Please let me know if this helps.

mhossain_0-1646550690739.png

 

View solution in original post

6 REPLIES 6
mhossain
Solution Sage
Solution Sage

Hello @mattao44 

 

Can you split the 'Code Mapping' table into two tables, one for CY and and other for PY, and then there will be relationship for these two tables with 'Clientcodejobcodes'. This will make the CY & PY as active relationship.

Hope this helps.

Hi @mhossain,

 

I was thinking of doing that but I need a way to associate the Current year and Prior year codes with eachother in order to perform analysis like Current year vs. Prior year Net Revenue.

@mattao44 

 

Can you share the dummy pbix here?

I have attached a simplified PBIX file below. I have also included the intended output I am looking for. The only thing to note is that there could be muliple rows in the Fact table for the same client code, therefore the measures would need to aggregate.

 

https://drive.google.com/file/d/1rcMj8VxbSgG3yy9JVvQuFaGbroLs4OXr/view?usp=sharing

 

2022-03-05_16-14-51.png

 

Hello @mattao44 

Attached is the pbix, with small changes in the mapping table, and added measures to the fact table, and see the table : Hope this is what was intented. If you don't want to change the mapping table structure it can be achieved using different method. Please let me know if this helps.

mhossain_0-1646550690739.png

 

Thanks @mhossain this works really well. It just wasn't making sense in my head on how to restructure the table to make more sense. Thanks again!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors