Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to write a couple DAX expressions to count the number of [Status] by [GroupKey] and maximum [TxDate]. For example, the end result should be counting the below records (in red) to show that [CountOfFailures] = 3 and [CountOfSuccess] = 2.
Any help would be greatly appreciated.
Identity | TxDate | Status | GroupKey |
00001 | 9/29/2020 0:00 | Failure | ABC |
00002 | 9/29/2020 0:00 | Failure | ABC |
00003 | 9/29/2020 0:00 | Failure | ABC |
00004 | 9/30/2020 0:00 | Success | ABC |
00005 | 9/25/2020 0:00 | Failure | XYZ |
00006 | 9/27/2020 0:00 | Failure | XYZ |
00007 | 9/28/2020 0:00 | Failure | XYZ |
00008 | 9/29/2020 10:00 | Failure | XYZ |
00009 | 9/29/2020 13:00 | Failure | XYZ |
00010 | 9/29/2020 15:00 | Failure | XYZ |
00011 | 9/26/2020 0:00 | Success | EFG |
00012 | 9/27/2020 0:00 | Success | EFG |
00013 | 9/28/2020 0:00 | Success | EFG |
00014 | 9/28/2020 0:00 | Failure | LMN |
00015 | 9/29/2020 0:00 | Failure | LMN |
00016 | 9/30/2020 22:00 | Failure | LMN |
00015 | 9/26/2020 0:00 | Failure | QRS |
00018 | 9/30/2020 00:00 | Failure | QRS |
Currently my DAX expression counts all the failures but my requirement is to group by the max date and GroupKey.
Solved! Go to Solution.
[# Failures] =
CALCULATE(
COUNTROWS( T ),
TREATAS(
ADDCOLUMNS(
DISTINCT( T[GroupKey] ),
"@MaxDate",
calculate( max( T[TxDate]) )
),
T[GroupKey],
T[TxDate]
),
// If you want "Sucess", change
// "Failure" to "Success" in the
// filter.
KEEPFILTERS( T[Status] = "Failure" )
)
[# Failures] =
CALCULATE(
COUNTROWS( T ),
TREATAS(
ADDCOLUMNS(
DISTINCT( T[GroupKey] ),
"@MaxDate",
calculate( max( T[TxDate]) )
),
T[GroupKey],
T[TxDate]
),
// If you want "Sucess", change
// "Failure" to "Success" in the
// filter.
KEEPFILTERS( T[Status] = "Failure" )
)
@joulethrex , Please find the file attached after signature.
A measure like
Measure = CALCULATE(COUNT('Table'[Identity]) ,FILTER(('Table'),'Table'[Identity] =CALCULATE(max('Table'[Identity]),ALLEXCEPT('Table','Table'[GroupKey]))))
Hello @amitchandak
Thanks for responding! I should have clarified that the max identity field isn't always going to be the same record as the one with the most recent TxDate. In fact, the [Identity] column should actually be ignored. I wasn't able to revise the sample because of some HTML issues but essentially the Identity column is not reliable in this scenario.
[CountOfOutstandingFailures] = 3
[CountOfOutstandingSuccess] = 2
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
40 | |
20 | |
12 |