how to get only one occurrence of distinct count

[lkrishnaprasad]

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

[solentking1]

Is there a way to do this in PowerQuery (M) for Excel?

[Ashish_Mathur]

Hi,

In your base data, do you not have a date column?

[lkrishnaprasad]

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

[Zubair_Muhammad]

@lkrishnaprasad

What is your expected result in this case?

[Zubair_Muhammad · Accepted solution]

@lkrishnaprasad

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 )

[lkrishnaprasad]

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. 

[Zubair_Muhammad]

@lkrishnaprasad

Could you paste some data with expected outcome?

Just like you did at the beginning of the post

[Zubair_Muhammad]

@lkrishnaprasad

Then we can use this column in a TABLE VISUAL

Please see file attached