Forum Discussion

kes7moodley's avatar
kes7moodley
Regular Visitor
2 years ago
Solved

Dax calculation to related table

Hi All   I am newish to powerbi, still have a lot I need to learn. 🙂 Hoping the community can help me with an issue I am trying to solve. I have 4 tables in my data model. I am drawing data from ...
  • scee07's avatar
    scee07
    2 years ago

    Hi, 
    seeing just a rough sketch of your model, it might help to think about what is not canoncial in your model. What means canonical: in the standard business scenario there is a transaction table (here: the lead table), a product category table and some subcategory tables. You seem to have 3 of them: let's name [Product Category], [Product SubCategory] and [ProductSubSubCategory]. In the canocial model your transaction table has a product ID and a SubSubCategory Field. Now the assumption is that every product ID is in exactly one SubSubCategory. 

    (In other words: if there is a relationship setup between the transcation table and the SubSubCategory table, it is n to 1, n transacations can be in the SubSubCategory]. Now every SubSubCategory has exactly one SubCategory. We say the the SubCategory is on the 1-side of the relationship. Similarly the Category table is on  the 1-side of the relationship to the SubCategory table. 

    If this all holds the pivot in Excel or matrix visual in Power BI will give you what you want. 

    See the typical Contoso model below:

    So, my guess is that you would have to fix your data model. If it is more complicated, it might be really reasonable to post a toy model as suggested below.

    Best regards 

    Christian

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi kes7moodley ,

According to your description, there are four tables(Lead, Product 1, Product 2 and Product 3) in your model. And you create the relationship among these tables base on different field. Do you want to get the count of item at different levels? Could you please provide some fake data in these tables (exclude sensitive data) with Text format, the relationship info and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

 

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards