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 resul...
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.