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.
So I have tables A, B and C
Table A is the fact table and table B is related to table A in a One-to-many relationship.
Table B is related to table C in a One-to-many relationship as well.
Table A and Table C have no direct relationship. However, they are both related to table B.
I want the amount from the cost column in table A to go under the product description column from table C in a matrix.
I wrote the following below but I'm not sure it is correct or even needed. Do I just need to switch my relationship filtering settings? Joining table A to table C would be many to many. Is that even allowed in Power BI?
Sales Amount =
CALCULATE (
SUMX ( Table_A, Table_A[Cost] * Table_A[Quantity] ),
Table_C
)
Thanks.
Solved! Go to Solution.
It's going to be dependent on the cross-filter directions of your relationships. If Table A filters your bridge table, Table B and Table B filters Table C then you have a many-to-many relationship in your case or vice versa. I'd have to see the relationship map.
In theory, you should not need that calculation since it is a simple SUM essentially. You can have many-to-many in Power BI with a bridge table as you have in your model. I'd have to model out what you are doing to be certain, can you supply sample data?
I'm sorry, I don't have any generic sample data. You say that it is a normal SUM since the amount is already stored on Table A and no calculation is needed. That part makes sense to me, but since there is no relationship I can't use RELATED or RELATEDTABLE.
To check,
I created a calculated column on table B
Transaction Amount =
SUMX (
RELATEDTABLE ( Table_A) , Table_A[Expenses] * 1)
I get unique values here for my new column but when I drag the new field into the matrix I get the same number repeating
I made the expenses a row in the matrix visualization instead of a value and that gave me the unique numbers I needed. Any idea why it works that way?
Thanks.
It's going to be dependent on the cross-filter directions of your relationships. If Table A filters your bridge table, Table B and Table B filters Table C then you have a many-to-many relationship in your case or vice versa. I'd have to see the relationship map.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |