Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Icon for Helper III rankHelper III
8 years ago
Solved

Creating lookthrough from 1 Fact table to another Fact table

I have 2 Fact Tables and I want to be able to create a pie chart with a hierarchy using the info from both tables. Fact Table 1: Is a Client Table showing the investment products the client owns.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Johnsnowlife,

    Please review this modified PBIX file.

    Regards,
    Lydia

Johnsnowlife's avatar
Johnsnowlife
Icon for Helper III rankHelper III
8 years ago

Ok, that makes sense. But if I make the values on the pie chart equal the Lookthrough instrument Holding = (% of fund in Client portfolio) * (% of instrument in Fund) then both levels will be correct. 

 

I'm not sure how I get the relationships to work so that I can still use 2 fact tables. Can you suggest the DAX to get the formula above? 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Johnsnowlife,

Could you please explain that which table the date field in Results Sheet of Excel file comes from? I note that it only shows date 2/29/2013.

Regards,
Lydia

  • Johnsnowlife's avatar
    Johnsnowlife
    Icon for Helper III rankHelper III
    8 years ago

    I only entered the date 2/29/2013 in the excel cell to make it faster to explain. But in PBI I would have selected one of the dates in a slicer. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Johnsnowlife,

    Which table do you drag the date field to create slicer and expected table visual?

    Regards,
    Lydia

  • Johnsnowlife's avatar
    Johnsnowlife
    Icon for Helper III rankHelper III
    8 years ago

    From my Calendar Table which is a proper time intelligence table. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Johnsnowlife,

    Please review this modified PBIX file.

    Regards,
    Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Johnsnowlife  have you got this working , if so could you please share the logic or .pbix file . am unable to extract the file from onedriver , i guess its been removed (since this conversation was a long time ago) . Thanks