Counting followup cases

[Anonymous]

Hello,

I'm quite new to PowerBI, I need some assistance on trying to count the number of followup cases. I will give an example below.

If I have a table which has the following information:

Date	Case Number
2020-04-20	1
2020-04-20	2
2020-04-20	3
2020-04-20	4
2020-04-22	1
2020-04-23	1
2020-04-30	2
2020-04-27	3

I'm trying to count the number of followup cases, possibly a column that flags that the occurance of this case is a followup. My final result is to get a table to plot the number of cases and number of followup cases over time.

I'm thinking if I can get a measure/column that would produce a result as follows, it would help:

Date	Case Number	IsFollowup
2020-04-20	1	0
2020-04-20	2	0
2020-04-20	3	0
2020-04-20	4	0
2020-04-22	1	1
2020-04-23	1	1
2020-04-30	2	1
2020-04-27	3	1

Hope the above made sense. Thank you 

[amitchandak]

@Anonymous , Create a sub category column rank and -1 from it you will get this.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Rank dates within case no.

[harshnathani]

HI @Anonymous ,

You can try this measure

Flag Cases = 
VAR cnt = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Case Number] ) )
RETURN

SWITCH(
    TRUE(),
    MAX('Table'[Date]) = MINX(ALLEXCEPT('Table','Table'[Case Number]),'Table'[Date]) , 0,
    cnt > 1, 1, 0
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

@harshnathani 

Hello, for some reason my flag case isn't doing what yours does. Instead it's flagging all of them as 1 even if it's the first instance the case has appeared. Basically if a case number has more than one it flags it regardless of whether it's the first instance or the subsequent instances. 

[v-lili6-msft]

hi @Anonymous 

If you do the filter in the report? if so, please use ALLSELECTED instead of ALLEXCEPT as below:

New Flag Cases = 
VAR cnt = CALCULATE ( COUNTROWS ( 'Table' ), FILTER(ALLSELECTED(  'Table'), 'Table'[Case Number] =MAX('Table'[Case Number])) )
RETURN

SWITCH(
    TRUE(),
    MAX('Table'[Date]) = MINX(FILTER(ALLSELECTED(  'Table'), 'Table'[Case Number] =MAX('Table'[Case Number])),'Table'[Date]) , 0,
    cnt > 1, 1, 0
)

If not your case, please share your sample pbix file and your expected output, that will be a great help.

You can upload it to OneDrive for business and post the link here. Do mask sensitive data before uploading.

Regards,

Lin

[Anonymous]

This didn't seem to work either. It flagged them all 0. I found a different way to do it. I used rankx to get the index per case, then I counted all the indices that were > 1 so that should only count the followups. 

[v-lili6-msft]

hi  @Anonymous 

Please share your sample pbix file for us to have a test.

You can upload it to OneDrive for business and post the link here. Do mask sensitive data before uploading.

Regards,

Lin