Forum Discussion

carloscabreraq's avatar
carloscabreraq
Helper I
7 years ago
Solved

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

 

 

 

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

 

 

Thanks for your help, regards.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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] )

     

    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.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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] )

     

    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.