Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
Usually, we have one invoice number per client or many invoice number for the same client but it may append that a mistake is done and that we have the same invoice number but for two different client.
So, if you look at the table below, the invoice 0048131 is repeted 3 times, 2 times for the client UBC PROPERTIES and one time for the client DISTRICT OF PORT EDWARD.
So, instead of getting 21 distinct invoice number, we would like to obtain 22 because an invoice number is associated with two different clients.
I have try
No of distinct invoice=calculate(distinctcount('table'[invoice]), filter(Table[client])) but I have an error message telling that we cannont convert a value of type text to type true and false)
Does someone know how to solve this issue?
id | Description | Invoice |
1124 | CITY OF PENTICTON | 0004060 |
1028 | CITY OF PENTICTON | 0004060 |
1027 | UBC PROPERTIES | 0048131 |
1122 | UBC PROPERTIES | 0048131 |
1122 | UBC PROPERTIES | 0054993 |
1027 | UBC PROPERTIES | 0054993 |
1027 | CLINTON & DISTRICT ASSISTED LI | 0056718 |
1122 | CLINTON & DISTRICT ASSISTED LI | 0056718 |
1027 | RF BINNIE AND ASSOCIATES LTD | 0058534 |
1122 | RF BINNIE AND ASSOCIATES LTD | 0058534 |
1027 | VANCOUVER COASTAL HEALTH | 0059510 |
1122 | VANCOUVER COASTAL HEALTH | 0059510 |
1027 | RF BINNIE AND ASSOCIATES LTD | 0060554 |
1122 | RF BINNIE AND ASSOCIATES LTD | 0060554 |
1027 | CLINTON & DISTRICT ASSISTED LI | 0060564 |
1122 | CLINTON & DISTRICT ASSISTED LI | 0060564 |
1027 | VANCOUVER COASTAL HEALTH | 0060713 |
1122 | VANCOUVER COASTAL HEALTH | 0060713 |
1027 | UBC PROPERTIES | 0062635 |
1122 | UBC PROPERTIES | 0062635 |
1027 | VANCOUVER COASTAL HEALTH | 0062758 |
1122 | VANCOUVER COASTAL HEALTH | 0062758 |
1122 | RF BINNIE AND ASSOCIATES LTD | 0062934 |
1027 | RF BINNIE AND ASSOCIATES LTD | 0062934 |
1027 | RF BINNIE AND ASSOCIATES LTD | 0063278 |
1122 | RF BINNIE AND ASSOCIATES LTD | 0063278 |
1027 | CB DEVELOPMENTS | 0064000 |
1122 | CB DEVELOPMENTS | 0064000 |
1027 | UBC PROPERTIES | 0064322 |
1122 | UBC PROPERTIES | 0064322 |
1122 | CB DEVELOPMENTS | 0064329 |
1027 | CB DEVELOPMENTS | 0064329 |
1027 | CITY OF WHITE ROCK | 0064377 |
1122 | CITY OF WHITE ROCK | 0064377 |
1027 | CITY OF CAMROSE | 0064379 |
1122 | CITY OF CAMROSE | 0064379 |
1027 | CITY OF WHITE ROCK | 0064382 |
1122 | CITY OF WHITE ROCK | 0064382 |
1027 | H20 ENTERPRISES INCORPORATED | 0065497 |
1122 | H20 ENTERPRISES INCORPORATED | 0065497 |
1123 | DISTRICT OF PORT EDWARD | 0896871 |
1124 | DISTRICT OF PORT EDWARD | 0048131 |
Solved! Go to Solution.
@Anonymous
Try this..
Measure = SUMX(SUMMARIZE('Table','Table'[Description],"Cnt",DISTINCTCOUNT('Table'[Invoice])),[Cnt])
See below
If it helps, mark it as a solution
Kudos are nice too
@Anonymous
Try this..
Measure = SUMX(SUMMARIZE('Table','Table'[Description],"Cnt",DISTINCTCOUNT('Table'[Invoice])),[Cnt])
See below
If it helps, mark it as a solution
Kudos are nice too
I decide to make a new column
User | Count |
---|---|
85 | |
84 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |