Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Anyone?
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:
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:
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 -
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
@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".
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |