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.
Resident RockstarSo you likely need to use ALL for part of your calculation. I think this is how you want to change your query, but this is massive and I might be wrong.
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), MiA <= FLOOR(YEARFRAC(CUR_TRNE[BirthTimestamp], TP_DTL[EventDate]), 1)
&& FLOOR(YEARFRAC(CUR_TRNE[BirthTimestamp], TP_DTL[EventDate]), 1) < MaA
)
--, USERELATIONSHIP(CUR_TRNE[BirthTimestamp], EDW_Cal[DayDate]) -- Still all 1s and blanks
--, USERELATIONSHIP(TP_DTL[EventDate], EDW_Cal[DayDate]) -- Still all 1s and blanks
)
)
Helper VA good idea, but that was the first thing I tried, and it just returned 1s and blanks.
I've also been trying to figure out how to fit in a CALCULATETABLE(), but haven't had any luck.
- Cmcmahan
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 ) ) )- BillyT_350
Cmcmahan You make a good point, and it just so happens that I have all of the other trainees, and by extension their birthdays, in the same table as the event details. See my next post.