Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |