Search code examples
dax

DAX move measure result from attribute values to other value

I have the following measures for a list of teams

Team Contract hours Flexteam hours worked for team
Team A 10 3
Team B 12 2
Team C 8 1
Flexteam 9 1

Problem with this data is that there is a single team, 'Flexteam', that works its hours for other teams as shown in the second measure. The question at hand is to show the hours worked for the given team. The definition of [Hours worked for team] is [Contract hours] plus a correction for these 'flexteam hours'.

The correction is as follows: [Flexteam hours worked for team] for all Teams except Flexteam. Flexteam gets the negative sum of [Flexteam hours worked for team] except for own [Flexteam hours worked for team]. This means that the hours worked for Flexteam by Flexteam would be ignored. The result would be as follows:

Team Correction flexteam Hours worked for team
Team A 3 13
Team B 2 14
Team C 1 9
Flexteam -6 3

I have defined a measure that is able to show me all [Flexteam hours worked for team] except on 'Flexteam':

[Flexteam hours worked except Flexteam] = 
CALCULATE(
    [Flexteam hours worked for team], 
    FILTER(Team, Team[Team] <> "Flexteam")
)

However I can't get a measure to show me the negative sum of [Flexteam hours worked except Flexteam] on 'Flexteam'. I have tried the formula that follows, I have tried nested and a single CALCULATE(), given KEEPFILTERS() a try but haven't gotten the desired result.

[Flexteam hours worked except Flexteam negative sum on Flexteam] = 
CALCULATE(
    CALCULATE(
        [Flexteam hours worked except Flexteam]*-1, 
        ALL(Team)
    ), 
    FILTER(Team, [Team]="Flexteam")
)

These get me the following, -6 should only be listed for Flexteam.

Team Flexteam hours worked except Flexteam Flexteam hours worked except Flexteam negative sum on Flexteam
Team A 3 -6
Team B 2 -6
Team C 1 -6
Flexteam -6

Anyone that can help me sort my DAX out?

Requested edit with diagram

I have left out columns irrelevant to this question. However, both initial measures come from different fact tables. I don't think that's really relevant though, since the issue only comes from a single measure using an attribute from a many-to-one-relationship.

Team

TeamID Team
1 Team A
2 Team B
3 Team C
4 Flexteam

Contracts

TeamID Hours
1 10
2 12
3 8
4 9

Active relationship [TeamID] many-to-one Team[TeamID]

Contract hours = SUM([Hours])

Roster

EmployedByTeamID WorkedAtTeamID Hours
4 1 3
4 2 2
4 3 1
4 4 1

Active relationship [WorkedAtTeamID] many-to-one Team[TeamID]

There is no relationship from [EmployedByTeamID] to Team. There is however a relationship to a calculated dimension thats a derivative of Team called Employed by team:

Active relationship [EmployedByTeamID] to 'Employed by team'[TeamID ]

Hours worked = 
    SUM([Hours])

Flexteam hours worked for team = 
    CALCULATE(
        [Hours worked], 
        FILTER('Employed by team', [Team] = "Flexteam")
    )

Diagram

Solution

  • Hopefully this helps, if not let me know what is not correct. I used random values for contract hours.

    You don't always need FILTER in CALCULATE. You can use RELATEDTABLE to force PowerBI to use the related table where it doesn't do it automatically.

    ContractHours = CALCULATE(SUM(Contracts[Hours]), RELATEDTABLE(Team))

    RosterHours = CALCULATE(SUM(Roster[Hours]), EmployedByTeam[Name] = "Flex Team", RELATEDTABLE(Team))

    Contract Plus Flex = [ContractHours] + [RosterHours]

    The next two do need a filter because we want to apply a filter to the team column before applying the calculation to remove the other teams. If you need positive values express as ABS().

    Hours Worked Except Flex Team = CALCULATE([ContractHours] - [Contract Plus Flex], FILTER(Team, Team[Name] <> "Flex Team"))

    Flex Hours Worked Except Flex Negative = CALCULATE([ContractHours] - [Contract Plus Flex], FILTER(Team, Team[Name] = "Flex Team"))

    Finally, using an IF statement to put the difference in the last line of the table:

    Flex Hours Worked Except Flex Negative 2 = 
VAR RosterVar = SUM(Roster[Hours]) + [Flex Hours Worked Except Flex Negative]
VAR TeamName = MAX(Team[Name])
RETURN
IF(TeamName = "Flex Team", -1*(RosterVar), BLANK())

    You can see the results below for the measures:

    Flex Hours Revised

    As an aside, it is much better to try put your data into a Star Schema with a single fact table. That will result in having to write simpler DAX. Even if you have to use a second inactive relationship (e.g. rather than two dimensions with TeamID). You can activate the relationship in a calculation with USERELATIONSHIP as needed.

    If you have to filter one dimension by another through a fact table then CROSSFILTER with direction "both" in CALCULATE.

    If all else fails then TREATAS will allow you to explicitly put a filter on a column, like where your data lineage is broken. But normally better solutions exist.

    Flex Model