Forum Discussion
Count only values with most recent date
I am trying to get the count for pie chart where only latest values for specific account should be counted. I can get them but with creating table, however I want to do it with DAX only so I do not need to store the table.
I was trying to use summarize, but I can't manage to use columns from virtual table to filter table
| Health | Date | Account |
| Green | 01 April 2022 | Account1 |
| Red | 01 March 2022 | Account1 |
| Green | 01 April 2022 | Account2 |
| Yellow | 01 May 2022 | Account2 |
| Red | 01 February 2022 | Account3 |
DAXOnlyCount =
var vTable =
SUMMARIZE ( ALL ( 'Table' ), 'Table'[Account], "Latest", MAX ( 'Table'[Date] ) )
return
CALCULATE(count('Table'[Health]),'Table'[Account]=vTable /*[Account] column*/,'Table'[Date]=vTable /*[Latest] column*/) //I get this is incorrect, I am just not sure how to do it. ))
Desired Output:
Thanks, but this only returns the latest value overall. I need latest value by each account.
In case of Account1 that is Green, as date for it is Apr 01, for Account2 it is Yellow since it's on May 01 and for account 3 it is Red, only one value.Hi Krcmajster ,
You can create a measure as below to get it, please find the details in the attachment.
Count with most recent date = VAR _latestdate = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Account] ) ) RETURN COUNTX ( FILTER ( ALLEXCEPT ( 'Table', 'Table'[Account] ), 'Table'[Date] = _latestdate ), [Health] )Best Regards
3 Replies
Krcmajster , try if one of the two can help
DAXOnlyCount =
var vTable =
Maxx ( ALL ( 'Table' ), 'Table'[Date] )
return
CALCULATE(count('Table'[Health]), 'Table'[Date]=vTable /*[Latest] column*/)or
DAXOnlyCount =
var vTable =
Maxx ( ALLexcept ( 'Table','Table'[Account] ), 'Table'[Date] )
return
CALCULATE(count('Table'[Health]), 'Table'[Date]=vTable /*[Latest] column*/)Thanks, but this only returns the latest value overall. I need latest value by each account.
In case of Account1 that is Green, as date for it is Apr 01, for Account2 it is Yellow since it's on May 01 and for account 3 it is Red, only one value.Hi Krcmajster ,
You can create a measure as below to get it, please find the details in the attachment.
Count with most recent date = VAR _latestdate = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Account] ) ) RETURN COUNTX ( FILTER ( ALLEXCEPT ( 'Table', 'Table'[Account] ), 'Table'[Date] = _latestdate ), [Health] )Best Regards
