cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

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 people who were in the same age group at the same time. I’m having trouble calculating this.

My report has three core tables, one with the current info of the people (Trainees/CUR_TRNE), another with the training periods those people have been through with the company (Training PRDs), and the third being the events that have occurred during those training periods (TP_DTL).

 

The below DAX calculated column code will run, but it will only return 1 for each row. I believe it is simply looking at the Trainees which are related to the event, there only being one because, of course, only one is related to that event. I think that I need to get DAX to ignore these existing table relationships and to just simply compare all of the data in Trainees with the data from the current record in TP_DTL.

 

I’d appreciate any help you can give.1.png

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(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
        )
    )
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Get 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 ) ) )
Super User
Super User

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

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.

8 REPLIES 8
Super User
Super User

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

So 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
        )
    )
jengwt Member
Member

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

A 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.

Super User
Super User

Re: Get 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 ) ) )
jengwt Member
Member

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

@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.

jengwt Member
Member

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

Let 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
            )
        )
    )
Super User
Super User

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

So then does it work?  Or are there still issues?

jengwt Member
Member

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

@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...

Super User
Super User

Re: Get DAX to Ignore Existing Table Relationships for Calculated Column

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 396 members 4,595 guests
Please welcome our newest community members: