DAX COUNTROWS - Count only last date occurence

[AKSteffensen]

Hi,

I have data on the following form:

Case key	Start Date	Case Type	Case Status
A-1	11-05-2019	A	Open
A-1	12-05-2019	A	In progress
A-1	12-05-2019	A	Refining
A-1	13-05-2019	A	Closed
B-2	11-05-2019	B	Open
B-2	11-05-2019	B	In progress
B-2	12-05-2019	B	Developed
B-2	13-05-2019	B	Refining
B-2	13-05-2019	B	Closed

I would like to create a DAX measure, that counts the amount of cases, which i can then split in graphs using different legens etc. However, when a case has multiple statusses in a single date, i only want the DAX measure to consider the last row, i.e. status "Refining" for case A-1 on 12-05-2019, "In progress" for case B-2 on 11-05-2019 and "Closed" for case B-2 on 13-05-2019. 

I am currently counting the rows using

Row Count = COUNTROWS( DISTINCT ( 'IssueHistory_FilledDates' ) )

Is there a neat way to make COUNTROWS only consider the last row, when there are multiple observations for a case on the same date?

Thank you in advance!

Best regards,

Andreas

[v-lili6-msft]

hi, @AKSteffensen 

You could add the two measure

last occurence = CALCULATE(MAX('Table'[Case Status]),FILTER(ALLSELECTED('Table'),'Table'[Case key]=MAX('Table'[Case key])&&'Table'[Start Date]=MAX('Table'[Start Date])))

Count result = 
 CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Case Status]=[last occurence]))

If not your case, please share the expected output.

Best Regards,

Lin

[AKSteffensen]

Hi @v-lili6-msft ,

Thank you for the answer. Unfortunately, it does not seem to work for what I need. Say i have the table outlined above. I then basically need a measure that counts the amount of rows. However, only the last observation should be counted if a case key has two observations with the same start date. Say i want to visualize the count measure based on 'Start Date' and 'Case Status'. The desired output would then be: 

Start Date	Case Status	Count
11-05-2019	Open	1
11-05-2019	In progress	1
12-05-2019	Refining	1
12-05-2019	Developed	1
13-05-2019	Closed	2

Obviously, the counts could be a lot higher, since there are many more cases in the actual data set.

Thank you for your time.

[v-lili6-msft · Accepted solution]

hi, @AKSteffensen 

The logic of measure is that, And what you want is add a conditional to filter case stats column that is not the last observation.

Otherwise, how to filter case status in the visual

So just try add these steps:

Step1:

In Edit Queries, add an index column (to determine which case status is the last for the same date)

Step2:

Add a calculate column that if the row case status is last case status

tag = IF(CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Case key]=EARLIER('Table'[Case key])&&'Table'[Start Date]=EARLIER('Table'[Start Date])))='Table'[Index],'Table'[Case Status])

Step3:

Use Measure and other fileds to create a visual and this column to filter the data is not blank

Result:

here is pbix file, please try it

Best Regards,

Lin

[AKSteffensen]

@v-lili6-msft - Thank you so much for the very fast reply.

I will give it a go and see if I can make it work!

UPDATE: Works like a charm, thank you!

Best regards,

Andreas