Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have data as shown belew
id | stage | status |
1 | a | complete |
1 | b | complete |
1 | b | not complete |
2 | a | complete |
2 | b | not complete |
I want to count number of id based on not complete status
so I have apply this Measure:
Measure = CALCULATE(
DISTINCTCOUNT(data[id]),FILTER(data,data[status]="not complete"))
but as you can see Id 1 have go to stage b tow times one with status complete and the other not complete,
so becouse I am focusing on not complete ids, I do not need to count the id if it on the same stage and with status complete.
based on the example the count of not complete will be 2 ,
but i need it to be 1 because i don't need to count id 1 if it is already complete on the same stage
Solved! Go to Solution.
I think this will work but please test properly with your data:
MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN
COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))
Each of the variables holds a table with the id and stage of the different statuses.
Count the 'not completed' rows. Subtract from this the count (of matching id and stage) rows that have a 'complete' and 'not complete' status.
hello, I cant use your solution,, here is my table, mame Tableau2
I want to count number CDPs distinct whith contition=Retard, result=2
TI test
Nom CDPs | Etat |
Creation nouveaux bureaux , broyeur, zone decheterie | Soldé |
Creation nouveaux bureaux , broyeur, zone decheterie | Soldé |
Creation nouveaux bureaux , broyeur, zone decheterie | Retard |
Creation nouveaux bureaux , broyeur, zone decheterie | Retard |
Creation nouveaux bureaux , broyeur, zone decheterie | Retard |
Creation nouveaux bureaux , broyeur, zone decheterie | Retard |
nouvelles références | Retard |
nouvelles références | En cours |
nouvelles références | En cours |
nouvelles références | En cours |
t imprimante | Soldé |
COUNTROWS(
SUMMARIZE(
FILTER(data,data[status]="not complete"),
[id]
)
thank you
but it is still the same , i need it first before count to check if the id with same stage have status complete if yes then, do not count it
if NO, count it.
i need to do this becouse there is a duplicate on id with same stage , so to solve it i have to ignore the id on not complet if it complet with same stage.
hope it is clear
I think this will work but please test properly with your data:
MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN
COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))
Each of the variables holds a table with the id and stage of the different statuses.
Count the 'not completed' rows. Subtract from this the count (of matching id and stage) rows that have a 'complete' and 'not complete' status.
is there any way to return the result as a table?
What would the table look like?
thank you it is working
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |