Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have to capture the count of Account for a specific region whose status is current = "Receive" but the value immediately prior to that should have been 'Call". My data is as below.
PrimaryID | AcctiD | Category | Start | KeyVal | Status | Sequence |
J1 | A1001 | East | 1/1/2020 | Open | Previous | 1 |
J2 | A1001 | East | 1/2/2020 | Call | Previous | 2 |
J3 | A1001 | East | 1/3/2020 | Receive | Current | 3 |
J4 | B1001 | West | 1/2/2020 | Open | Current | 1 |
J5 | C1001 | West | 1/5/2020 | Open | Previous | 1 |
J6 | C1001 | West | 1/6/2020 | Call | Current | 2 |
J7 | D1001 | East | 1/1/2020 | Open | Previous | 1 |
J8 | D1001 | East | 1/2/2020 | Receive | Current | 2 |
J9 | F1001 | East | 1/5/2020 | Call | Previous | 1 |
J10 | F1001 | East | 1/7/2020 | Receive | Previous | 2 |
J11 | F1001 | East | 1/8/2020 | Open | Previous | 3 |
J12 | F1001 | East | 1/9/2020 | Call | Previous | 4 |
J13 | F1001 | East | 1/10/2020 | Receive | Current | 5 |
So for the above table, my jan count that fits the requirement is 3 (A1001 on 1/3 and F1001 on 1/7 and F1001 on 1/10).
I tried this DAX, but i keep getting an error with Earlier function.
Rec_PreVal_Call = calculate(counta(AccHistory[AcctID]), filter (AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Receive" ), filter(AccHistory, AccHistory[Category] = "East" && AccHistory[KeyVal] = "Call" && Earlier(Keyval,1)))
Any recommendations on how to achieve this please.
Solved! Go to Solution.
Hi @PBI5851 ,
Try create a flag as below.
Column =
var last_date = CALCULATE(MAX('Table'[Start]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]<EARLIER('Table'[Start])))
var last_key = CALCULATE(MAX('Table'[KeyVal]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]=last_date))
return
last_key
Then you should be able to count the id.
Best Regards,
Jay
Hi @PBI5851 ,
Try create a flag as below.
Column =
var last_date = CALCULATE(MAX('Table'[Start]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]<EARLIER('Table'[Start])))
var last_key = CALCULATE(MAX('Table'[KeyVal]),FILTER(ALLEXCEPT('Table','Table'[AcctiD]),'Table'[Start]=last_date))
return
last_key
Then you should be able to count the id.
Best Regards,
Jay
Hi @PBI5851
Here's a measure that should work, if I've understood your requirements correctly:
Rec_PreVal_Call =
SUMX (
CALCULATETABLE (
SUMMARIZE ( ACCHistory, ACCHistory[AcctiD], ACCHistory[Sequence] ),
ACCHistory[KeyVal] = "Receive"
),
VAR Seq = ACCHistory[Sequence]
RETURN
CALCULATE (
INT ( SELECTEDVALUE ( ACCHistory[KeyVal] ) = "Call" ),
ACCHistory[Sequence] = Seq - 1
)
)
There are certainly alternative ways of writing the measure.
I avoided the EARLIER function in this case by using a variable for the current Sequence value (Seq).
Does this work in your model?
Regards,
Owen
Owen,
Thank you for the response. unfortunatly that will not work, as i cannot rely on the sequence field for this calculation, because there is a possibility for other Keyval values to come into the table and the The Receive row can sometimes be repeated which will negate the seq-1 criteria.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |