DAX: Calculating sum over relative table

[Z7-852]

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.

[Z7-852]

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 C

WHERE AId IS NOT NULL AND BId IS NOT NULL
GROUP BY AId, BId

[MattAllington]

It'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

[Z7-852]

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?"

[sdjensen]

If I understand you correct all you need is to load all 3 table into PBI Desktop and create relationships from c to a and c to b.

Create a measure that sum your number from c.

Finally add Id from a and b and the new measure in a matrix visual.

[Z7-852]

Matrix visual could be an easy answer if there weren’t over 2 million rows in both A and B. Also you don’t have any interaction in the matrix.

Ideal case would be if I could have to bar charts that are both using the SUM(C[Number]) as values and A and B respectably as rows. Both A and B have multiple “upper class” which I can use to drill down.

By selecting one row from A column the measure would answer “Is this A using all the Bs more or less than average A” and the B columns would answer “Compered to average how much more or less is A consuming this B?”

And when selecting column from B measure would answer “Is this B used more by all the As relative to average B” and A columns would answer “Compered to average how much B is consumed by this A?”

The measure should be something like SUM(visually filtered) / SUM(all).

I have managed to do this using SQL GROUP clause. But the problem is I cannot compere time periods. For example if some of the B:s have been replaced with other items.

[sdjensen]

What I meant with my answer was that you don't need any cross join, group by or any fancy SQL to do what you described all this will happen correctly if you have the right relationships in you model and create the right calculations, but it's really hard to come closer to a result with the info provided.

The Matrix was just an example to return the result as you described it almost any visual will be able to do the same. Like if you have a visual with aID and then bID in a hierarchy you can drill down from a member of a to see all the bID's that has values for this aID.

I don't think you will find any visual that will give you a nice view of 2 x 2,000,000 members and present in on a regular size monitor. You would have to filter these first before making any sence.