Forum Discussion

mattao44's avatar
mattao44
Frequent Visitor
4 years ago
Solved

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?

 

Need Help
  • mhossain's avatar
    mhossain
    4 years ago

    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

  • mhossain's avatar
    mhossain
    Solution Sage
    4 years ago

    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's avatar
      mattao44
      Frequent Visitor
      4 years ago

      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.