cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovonel
Post Prodigy
Post Prodigy

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

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)

 
3 REPLIES 3
v-yadongf-msft
Community Support
Community Support

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.

thanks @v-yadongf-msft 

 

 

Yes, I have this visual:

ovonel_0-1669895271693.png

 

 

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:

ProjRolesName
1RJohn
1BTim
1CLaura
2RHector
2BThomas
2CTim

 

 

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
Post Prodigy
Post Prodigy

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)

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors