Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have data like this
case | incident
----------------
c1 , inc1
c2 , inc1
c3 , inc2
c4 , inc2
c5 , inc3
when I do a matrix /table with showing distinct count(incident) I get like this
case | incident count
-----------------------
c1, 1
c2, 1
c3, 1
c4, 1
c5, 1
-----
3
what I expect is
case | incident count
-----------------------
c1, 1
c2, 0
c3, 0
c4, 1
c5, 1
-------
3
my total is correct, but each row is counting 1 because the same incident is attached to 3 cases.
please help how to get the correct count in rows as well as total.
Thanks
Krishna
Solved! Go to Solution.
One possible solution could be to add a calculated column assigning each incident to its first case
For example
Count = VAR FirstCase = CALCULATE ( FIRSTNONBLANK ( Table1[case], 1 ), ALLEXCEPT ( Table1, Table1[incident] ) ) RETURN IF ( Table1[case] = FirstCase, 1, 0 )
Hi,
In your base data, do you not have a date column?
Hi,
Sorry for the late reply. There are no date columns.
Here is my data.
PBI | INC | Table4.Column1 | Table4.Column2 |
PBI1 | INC1 | S1 | INC1 |
PBI1 | INC1 | S2 | INC1 |
PBI1 | INC2 | S3 | INC2 |
PBI1 | INC3 | S4 | INC3 |
PBI2 | INC4 | S5 | INC4 |
PBI2 | INC5 | NoData | null |
PBI3 | INC6 | NoData | null |
PBI3 | INC7 | NoData | null |
PBI3 | INC8 | NoData | null |
What is your expected result in this case?
One possible solution could be to add a calculated column assigning each incident to its first case
For example
Count = VAR FirstCase = CALCULATE ( FIRSTNONBLANK ( Table1[case], 1 ), ALLEXCEPT ( Table1, Table1[incident] ) ) RETURN IF ( Table1[case] = FirstCase, 1, 0 )
Thanks, it did work but if I have 1 more field?
Suppose if I have 1 more child field that relates to same PBI number, then what would be the formula to handle?
please see the output based on your query.
Could you paste some data with expected outcome?
Just like you did at the beginning of the post
Then we can use this column in a TABLE VISUAL
Please see file attached
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
76 | |
66 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |