cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carloscabreraq Helper I
Helper I

Many to Many - Measure

Hello,

I have 2 tables "sales" and "Type", with a many to many relationship (type).
The type Table has Type, SubType and Value columns

 

PowerBi Problem A.PNG

 

 

How can i do a measure that gets me this result by subtype, filtering Month = 3.

 

PowerBi Problem B.PNG

 

Thanks for your help, regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Many to Many - Measure

Hi @carloscabreraq 

You can refer to the following DAX formula to create a measure, which creates a virtual table to merge tables Sales and Type, and returns the result you want.

 

Measure =

VAR t =

    FILTER (

        CROSSJOIN (

            SELECTCOLUMNS ( Sales, "sale_type", [type], "id", [id], "month", [month] ),

            'Type'

        ),

        [sale_type] = [Type]

            && [month] = 3

    )

RETURN

SUMX ( FILTER ( t, [SubType] = MAX ( [SubType] ) ), [Value] )

Capture17.JPG

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Many to Many - Measure

Hi @carloscabreraq 

You can refer to the following DAX formula to create a measure, which creates a virtual table to merge tables Sales and Type, and returns the result you want.

 

Measure =

VAR t =

    FILTER (

        CROSSJOIN (

            SELECTCOLUMNS ( Sales, "sale_type", [type], "id", [id], "month", [month] ),

            'Type'

        ),

        [sale_type] = [Type]

            && [month] = 3

    )

RETURN

SUMX ( FILTER ( t, [SubType] = MAX ( [SubType] ) ), [Value] )

Capture17.JPG

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
carloscabreraq Helper I
Helper I

Re: Many to Many - Measure

hi @v-juanli-msft ,

in this solution we dont need the "many to many" relationship?

Community Support
Community Support

Re: Many to Many - Measure

Hi @carloscabreraq 

Yes.

 

Best Regards
Maggie

carloscabreraq Helper I
Helper I

Re: Many to Many - Measure

@v-juanli-msft is there another solution


@v-juanli-msft wrote:

Hi @carloscabreraq 

Yes.

 

Best Regards
Maggie



using the logical relationship?

 

Thanks for your time!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors