Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |