SUM of a measure ? in a model with Many To Many relations)

Anonymous — Sun, 04 Dec 2022 17:03:45 GMT

I have a model where I have my fact and Project Dimension:

But I also have a 1:* relationship between Project and Roles.

That is:

Fact * 1Project

Project1 * Roles (for a given project I have 3 ppl: ‘responsible’, ‘boss’, ‘control’)

I have this measure that I am using (in a matrix visual), where I display some custom aggregation:

My levels are:

End Customer
- Level 1
  - Level 2
    - Level 3

Result:=

VAR _part1 =SUMX ( SUMMARIZE (  FILTER (  ALL ( 'Fact' ),Fact[Level1] = MAX ( Fact[Level1] ) && Fact[Level2] IN VALUES (  Fact[Level2] )
),

            Fact[Level1],
            Fact[Level2]
        ),
       [Basic])
...

Return SWITCH (

    TRUE(),

    ISINSCOPE (Fact[Level3] ), [Basic],

    ISINSCOPE (Fact[Level2] ), [Basic],

    ISINSCOPE (Fact[Level1] ), _part1,

    ISINSCOPE (Fact[End Customer] ), _part2
)

Now , to the top level (in my matrix visual) I want to add Roles[Name] ... Of course I need a ISINSCOPE for this case:

ISINSCOPE (Roles[Name] ), xyz,

But I have tried writing the xyz code in every way and I can't make it work

(For all my other cases it works, because I bring the columns level1,level2, etc from the dim to the fact, but now I can’t do this because I have a * to * relationship).

(the agreggation shall be just a SUM of whatever is on End Customer)

This is my visual:

In Green is level3, and brown level2.

This works perfectly fine, with the measure described...

But, now i want to add a higher level: Roles[EmployeeName]

Re: SUM of a measure ? in a model with Many To Many relations)

tamerj1 — Sun, 04 Dec 2022 18:22:07 GMT

Hi Anonymous
Please try

Result := SUMX ( CROSSJOIN ( VALUES ( Roles[Name] ), SUMMARIZE ( 'Fact', Fact[Level1], Fact[Level2], Fact[Level3] ) ), [Basic] )

Re: SUM of a measure ? in a model with Many To Many relations)

Anonymous — Mon, 05 Dec 2022 12:24:03 GMT

thanks, but it didnt work, I dont understand why it can't just add the measure...

topic Re: SUM of a measure ? in a model with Many To Many relations) in DAX Commands and Tips

SUM of a measure ? in a model with Many To Many relations)

Re: SUM of a measure ? in a model with Many To Many relations)

Re: SUM of a measure ? in a model with Many To Many relations)