Forum Discussion
DAX: Calculating sum over relative table
I having troubles forming a calculations over relative table.
My data is in three tables (A, B and C). In C table there are rows that contain A.ID and B.ID and a number (#).
Tables A and B have no logical relationship.
I need a table with following cross join.
| A.ID | B.ID | # |
| 1 | 1 | SUM # |
| 1 | 2 | SUM # |
| 2 | 1 | SUM # |
| 2 | 2 | SUM # |
And so on.
Guestion that I'am trying to ask is "How much item B is used by item A?" and "Is usage of item B by item A more or less than avarage between all the As?"
If I try to use CrossJoin it gives me memory error because the cross join table is too large. I figured I have to use measure that calculates the SUM but I haven't figured out how to do it over two tables.
6 Replies
I solved this by using SQL but I would still like to know how to do this in DAX.
Solution was something like
SELECT C.AId, C.BId, SUM(number) OVER (PARTITION BY AId)
FROM CWHERE AId IS NOT NULL AND BId IS NOT NULL
GROUP BY AId, BIdIt's a bit hard to understand what you want. How about you build a small sample workbook, make the joins and show the output you are after
Table A
ID 1 2 3 4 5 Table B
ID 1 2 3 4 5 Table C (Consumption number of B by user A. Also contains data like date)
Almost all the information needed is in table C.
AID BID NUMBER 1 1 10 3 1 20 3 2 30 4 1 40 3 2 50 Result
AID BID SUM(number) 1 1 10 3 1 20 3 2 80 4 1 40 The SQL code that I posted worked and I was also able to use SQL to calculate other totals like (B1 usage in this example 70 and total A3 usage in this example 100).
Problem with SQL code that I used was that it removed time dimension from data because of the GROUP BY clause.
It would be nice if I had one measure that would tell me "Does this A consume more B than avarage A?"
