Forum Discussion
Helper VGet DAX to Ignore Existing Table Relationships for Calculated Column
In plain English, what is this calculation trying to show? If there is no event date, return blank. Otherwise, it seems to be trying to count all of the the people who took the training within the same age band as the current user. Is this correct?
Try this, which uses the birthday within the TP_DTL table instead of bouncing between TP_DTL and CUR_TRNE. I'm not sure if you want to use DISTINCTCOUNT instead of COUNT as your aggregation, so I left it as count. This will count each person multiple times if they took multiple trainings that match the filter:
AllDriversInAgeGroup = VAR A = TP_DTL[TeEventAge] VAR MiA = SWITCH(TRUE() , A < 20, 0 , A < 30, 20 , A < 40, 30 , A < 50, 40 , A < 60, 50 , A < 70, 60 , A >= 70, 70 , BLANK() ) VAR MaA = SWITCH(TRUE() , A < 20, 20 , A < 30, 30 , A < 40, 40 , A < 50, 50 , A < 60, 60 , A < 70, 70 , A >= 70, 999 , BLANK() ) RETURN SWITCH(TRUE() , ISBLANK(TP_DTL[EventDate]), BLANK() , CALCULATE(COUNT(CUR_TRNE[PersonCode])
, FILTER(ALL(CUR_TRNE)) , FILTER(ALL(TP_DTL), MiA <= FLOOR(YEARFRAC(TP_DTL[Driver Bday], TP_DTL[EventDate]), 1) && FLOOR(YEARFRAC(TP_DTL[Driver Bday], TP_DTL[EventDate]), 1) < MaA ) ) )Yeah, calculated columns only update on data refresh.
However, DAX Measures will re-calculate each time they are used. I'll take a look at your new post and see if I can help there.
Helper VLet night I saw Cmcmahan's respons, and remembered that I do have all of that data in the event detail table. So, it only became a question of getting DAX to remember the event date in question, and then doing a distinct evaluation of all of the other trainees. I think this is what I've done here:
[other variables and code in OP]
VAR E = TP_DTL[EventDate] -- This should only be for the CURRENT ROW, not for the population of rows being evaluated by the calculation below
RETURN
SWITCH(TRUE()
, ISBLANK(TP_DTL[EventDate]), BLANK()
, CALCULATE(DISTINCTCOUNT(TP_DTL[TePC])
, FILTER(ALL(TP_DTL), MiA <= FLOOR(YEARFRAC(TP_DTL[TeBDay], E), 1)
&& FLOOR(YEARFRAC(TP_DTL[TeBDay], E), 1) < MaA
)
)
)- Cmcmahan
Resident Rockstar
So then does it work? Or are there still issues?
- BillyT_350
Cmcmahan It works, but only if the user doesn't apply any filters. Now I have to figure out how to make it dynamic for when a user selects, say, a hierarchy filter. Having to go back to the drawing board on this one. I don't think you can have dynamic calculated columns.
For instance, say your per capita event occurence is 1. The user may select a segment of the company via a hierarchy slicer, and now it says the occurence is 0.33. The user thinks they have a better than average occurence of events, but in reality their org has about 1/3 of occurences which are still being weighted against the whole company's population, rather than their org's popultion. Their real per-capita event occurence rate may be like 1.5.I think I'm going to have to start a new thread for this one: https://community.powerbi.com/t5/Desktop/Highly-Dynamic-Per-Capita-Event-Rate-Calculation/m-p/740097#M356859
- Cmcmahan
Yeah, calculated columns only update on data refresh.
However, DAX Measures will re-calculate each time they are used. I'll take a look at your new post and see if I can help there.