Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help with distinctcount with filters from several tables!

Hi

I have 3 tables with following data. 

Table 1 - Reseller 

Country - Hierarchykey - Reseller - End Customer

Aus      - 123           -   Rondo   - Endcust1

Aus     -   453          -   Rondo   - Endcust2

NZ      -  564           -   Alfonso  - Endcust3
Table 2 - Charge 

Material code  - Currency - ChargeReseller - ChargeEnd Cust - HierarchyKey

1994424          - EUR        - 10.10                 -12.3                     - 123

1005570          -EUR          - 12.7                  - 12.8                    -636

1005570          -EUR          - 20                     - 22                      - 956    

Table 3 - Material 
Material code  -  Manufacturer Name   -  Description

1994424          -   Soap                          - Slippery

4950603          -  Leaf                            - Green

4958699          - Trunk                          - None

 

 

Based on these 3 tables I need to calculate how many distinct Resellers have used only one Manufacturer - Soap. And how many have all the other Manufacturers except the Soap. 

The formula should be smth like this -  Calculate Distinctcount of ResellerName, Filter where Manufacturer distinctcount is 1 AND it is Soap. 

The thing is - Each of resellers have many different Manufacturers they use so I think the main issue is to filter out the resellers that have only one specific. 

In Material table I already have the column that has "1" next to each Manufacturer when it is Soap, but can someone help me with correct formula to use? Or maybe help with creating join table? 

8 REPLIES 8
Anonymous
Not applicable

Anyone?

 

Anonymous
Not applicable

I guess that join table would do more harm as there are millions of rows and atm i'm working with small test data sample and would not want to join three tables on the real data, that might cause a total power bi death 😕 

Hi @Anonymous 

You can use the following measures:

Count of Reseller for Soap =
CALCULATE(
    DISTINCTCOUNT('Reseller'[Reseller]);
    'Material'[Manufacturer Name] IN { "Soap" }
)
Count of Reseller for none Soap =
CALCULATE(
    DISTINCTCOUNT(Reseller[Reseller]);
    Not(Material[Manufacturer Name] in {"Soap"})
)
In fact, there is another way to get what you want without using measures, and that is by just dragging the Reseller field to a table visual. Pick "Count (Distinct)" in Values, and drag Manufacturer Name to Filters pane to filter for "Soap". Then, to get the distinct count of resellers that have been used everything but "Soap", you can follow the same steps and in the filter pane instead of selecting "Soap" you select "All" then unselect "Soap".
Please find the .pbix file here
 
I hope that helped!
If this answers your question, please give Kudos and mark my answer as Solution 🙂
Anonymous
Not applicable

Hi @saraMissBI 
Thanks for the advice - it seems so simple, and I have tried it (at least similar distinctcount), but there is an issue, that this formula counts Distinct count of Resellers, but I also need it to filter that this reseller A- has only one Manufacturer (so distinctcount of Manufacturer is 1) and B - it is Soap. And I have tried so many different ways to achieve it, but with no luck so far. 

Seems that the issue here is that each Reseller have many Manufacturers and the all are in the charge table, so when I call for a reseller that has Soap, it selects it also if it has any of the other Manufacturers.  

 

Hi @Anonymous ,

Okay, could you please try the following?

- Create a table with the following formula:

Table = CALCULATETABLE(Reseller;
FILTER(Reseller;COUNTX(Material;Material[Manufacturer Name])))
- Update the formula of the measure:
Count of Reseller for Soap =
CALCULATE(
    DISTINCTCOUNT('Table'[Reseller]);
'Material'[Manufacturer Name] IN { "Soap" }
)
To be honest, I am not sure if that gives the correct result. Could you please try with your data model and see if that works?
 
Thank you
Anonymous
Not applicable

Hi @saraMissBI 
thanks, Im trying to create the table, but what you mean by Filter - Reseller, - If I'm inserting the table that i want to filter, the table formula in the end becomes different - 

Table = CALCULATETABLE('Reseller',
FILTER('Reseller', 'Reseller'[ResellerName]),COUNTX(MATERIAL, MATERIAL[ManufacturerName]))
and I get error, that COUNTX has been used in True / false expression that is not allowed. 

Hi @Anonymous ,

I see you changed the formula of table I shared with you. Please try to use the same exact formula, and just change the corresponding items (tables, columns) with the names of tables and columns you have in your real model based on the sample model you shared in your post.

 

Thank you

 

Anonymous
Not applicable

@saraMissBI 
I did create the exact table and changed the measurement as you suggested. In my datasample I have 9 Resellers and 8 of them have the Soap Manufacturer, one has others only; 

previously the formula always returned "8" for me, as I think it just counts all resellers and cannot filter out distinct reseller, that has ONLY one Manufacturer and ONLY Soap. Now with new table and new measurement I get "9". 

 


soap.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors