Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |