Multiple IDs to one fact table

[mattao44]

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?

[mhossain]

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.

[mattao44]

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.

[mhossain]

@mattao44 

Can you share the dummy pbix here?

[mattao44]

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

[mhossain · 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.

[mattao44]

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!