cancel
Showing results 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

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?

1 ACCEPTED SOLUTION
Solution Sage

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.

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

Frequent Visitor

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.

Solution Sage

Can you share the dummy pbix here?

Frequent Visitor

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.

Solution Sage

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.

Frequent Visitor

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!