Dax calculation to related table

[kes7moodley]

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 our D365 CE crm system.

To start with I am trying to count our leads, on the lead table  (Table 1) we have the product the customer may potentially buy in a product field (Lookup), we will call this field "Product 1". I have created a simple dax calculation that that counts the leadid "COUNT('lead'[leadid])+0)". I have added the zero at the end so my report does not have blanks. In a nutshell this calculation will say you have 100 leads and then break this down based on the value of "Product 1".

Related to the lead table via "Product 1" field,  we have a another table called for example "Product 1" (Table 2). This table lists all the "Product 1" we have in the organisation. The lead table to the product 1 table is a many to one relationship.

Related to the "Product 1" table we have another table called "Product 2" (Table 3). There can be various product 2 nested under product 1. An example could be "Product 1 = Lawnmower", "Product 2 = Petrol Lawnmower, Electric lawnmower". The relationship is via a field called "Product 1" field on the "Product 2" table. This relationship is also a many to one relationship.

Lastly we have line items that are stored in another table called "Product 3"(Table 4). There can be many line items nested under Product 2 via this table. The relationship is via a field called called "Product 2" on the "Product 3 table". This relationship is also a many to one relationship.

My model therefore looks like this Lead > Product 1 > Product 2 > Product 3.

And example of what I am trying to achieve looks something like this:

First Level:

Leads/Product 1 =1000 ( There can be various products: Lawnmower,Chain saw, Edge Trimmer etc)

Second Level (Nested under each product we have second level or specialisation type of that product , example Electric Lawn Mower, Petrol lawnmower)

Product 1

• Product 2 a) = 200 ( Electric Lawnmower)
• Product 2 b) = 600 (Petrol Lawnmower)
• Prodct 2 c) = 200 (Diesel Lawnmower)

Third Level: (Line Items, this could be a list of items that make up product 2, example could be for each electric lawnmower, you have 1 set of wheels,1 electric motor, 1 lawnmower casing, 1 grass bin, 1 plug, all of these make up one lawnmower). Therefore if I have 200 electric lawmowers, then I have 200 plugs, 200 electric motors, 200 grass bins, 200 casings/bodies and vice versa). Example below:

Product 2 a) = 200

• Product 3 a) 200 
• Product 3 b) 200
• Product 3 c) 200

Product 2 b)

• Product 3 d) 600
• Product 3 e) 600
• Product 3 f) 600

Apology for the log essay, I tried to explain this as clearly as possible without giving out senstive information. The issue I have is once I plug in my dax calculation i am only able to get to level 2. At level 3 it links every single line item to each of the level 2 present.  However if I remove my dax calculation and count the leadid directly in the values field then I am able to get to the lowest level.

Any ideas where I could be going wrong? Thanks for the help.

Regards

Kes

[kes7moodley]

Thank you all, aprreciate the help. 🙂

[scee07 · Accepted solution]

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

[v-yiruan-msft]

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