Forum Discussion

BillyT_350's avatar
BillyT_350
Icon for Helper V rankHelper V
7 years ago
Solved

Get DAX to Ignore Existing Table Relationships for Calculated Column

I’m trying to normalize the sum of the number of events per-capita. In the visuals, these are events per age group. So, to normalize them on a per-capita basis would mean I have to find the number of...
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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 ) ) )
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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.