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

[ovonel]

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

But I also have a 1:* relationship in 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]

    )





VAR _part2 =

    SUMX (

        SUMMARIZE (

            FILTER (

                ALL ( Fact ),

                Fact[End Customer] = MAX ( Fact[End Customer] ) && Fact[Level1] IN VALUES (  Fact[Level1] ) && Fact[Level2] IN VALUES (  Fact[Level2] )

            ),

            Fact[End Customer],

            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)

[v-yadongf-msft]

Hi @ovonel ,

In order to better understanding your demands and give the right solution, could you please share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ovonel]

thanks @v-yadongf-msft 

Yes, I have this 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]

As I mentioned, the model is:

Fact * - 1Project

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

the roles table has an structure like:

Proj	Roles	Name
1	R	John
1	B	Tim
1	C	Laura
2	R	Hector
2	B	Thomas
2	C	Tim

My expected output of the matrix would be levels:

• EmployeeName (this just being the sum of whatever appears in end customer)
  • End Customer
    • Level 1
      • Level 2
        • Level 3

[ovonel]

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

But I also have a 1:* relationship in 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]

    )





VAR _part2 =

    SUMX (

        SUMMARIZE (

            FILTER (

                ALL ( Fact ),

                Fact[End Customer] = MAX ( Fact[End Customer] ) && Fact[Level1] IN VALUES (  Fact[Level1] ) && Fact[Level2] IN VALUES (  Fact[Level2] )

            ),

            Fact[End Customer],

            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] ; so I need some sort of:

ISINSCOPE (Roles[Name] ), xyz,

I have tried every way and I can't make it work (for all the other cases it works to 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)