Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
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.
hi @v-juanli-msft ,
in this solution we dont need the "many to many" relationship?
@v-juanli-msft is there another solution
@v-juanli-msft wrote:Yes.
Best Regards
Maggie
using the logical relationship?
Thanks for your time!
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |