Comparing two fact tables

[TonyBI]

Hello my friend, I´d really appreciate some help regarding a data modeling issue. I´ll try to explain myself:

I have two fact tables that contain many items that I need to be able to compare. Table A contains items and values for every day, and Table B contains Reference Items and values for every day. I need to be able to select many items from table A and compare them with a selected Reference Item from table B. This need to happen in a line chart (with the legend of every item selected and, if possible, the referenceItem too). 

Thx in advance!

[TonyBI]

Hello folks, sorry to bother you again, but I´m really strugling with this problem. Original solution was really fine until I have to calculate cumulatives, they are really really slow. It seems to be checking for every item if the condition is ok (the metric in the solution), and then calculating the cumulative. The main issue is that I need to select items and references (the have their own dimension and fact tables), and show them in the same line chart with legend (the reason here is because I need to compare any or many items with any or many references).

Do you have any other work around? Thx very much!

[sanimesa]

@TonyBI   Without seeing a bit of the data and expected results, My best guess about your requiement:

1. Assuming items andd refence items all have distinct names. If not, you will need to apply a prefix to the ref items. 

2. Merge the two tables. 

3. Create a line chart where X axis is date from the merged table. Add the value to the value.

4. Add the merged item number to the legend of the chart.

4.  Add a slicer with the item name - user can select multiple items and ref items - and since the ref items are differentiated by name, you know which ones they are.

[amitchandak]

@TonyBI , Need more information.  You can create common dimensions.  Like item

Item = distinct(union(all(Table1[Item]),all(Table2[Item]))).

Join both tables and use them.

Also, create a common date dimension

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[TonyBI]

Amitchandak, thx for your reply! unfortunately this is not what Im looking for, because the problem is that the tables contains so many items that mergin them is ok, but then select a small number is a pain. I can build a dimension table but item and reference tables have diferent grouping columns. Here is an example:

Item table:

Item	Day	Value
A	1	12
A	2	31
A	3	23
A	4	12
A	5	31
A	6	42
A	7	10
A	8	40
A	9	53
A	10	50
A	11	36
B	1	40
B	2	10
B	3	34
B	4	30
B	5	23
B	6	4
B	7	34
B	8	54
B	9	89
B	10	56
C	1	74
C	2	42
C	3	45
C	4	69
C	5	47
C	6	55
C	7	45
C	8	40

Refernce table

Item	Day	Value
Refrence 1	1	7
Refrence 1	2	26
Refrence 1	3	18
Refrence 1	4	70
Refrence 1	5	34
Refrence 1	6	65
Refrence 1	7	87
Refrence 1	8	12
Refrence 1	9	33
Refrence 1	10	22
Refrence 1	11	31
Refrence 2	1	45
Refrence 2	2	67
Refrence 2	3	54
Refrence 2	4	25
Refrence 2	5	18
Refrence 2	6	-1
Refrence 2	7	29
Refrence 2	8	49
Refrence 2	9	84
Refrence 2	10	51
Refrence 2	11	69
Refrence 2	12	37
Refrence 2	13	40
Refrence 2	14	64
Refrence 2	15	42
Refrence 2	16	50
Refrence 2	17	40
Refrence 2	18	35

Expected result:

Result

I can do this by mergin item and reference fact tables, but the problem is that I want to be able to select an item from its own dimension table, and a reference from its own reference dimension table (this is a must because of the amount of items and references, and because of the many different grouping that this two dimensions tables have).

Little example for dimension tables:

Item dimension:

Item	Colour	Shape	Country
A	Blue	Square	Argentina
B	Red	Triangle	Brasil

Reference dimension:

Reference	Size	Weight	approved
Refrence 1	Big	Light	Yes
Refrence 2	Small	Heavy	Yes

Thx!!

[v-jayw-msft · Accepted solution]

Hi @TonyBI ,

Here's the thing, If we put Item column in Legend field then the Values field can contain only one column. So generally, we need to union these two table as one.

My advice is to create a calculated table with below formula then create a measure and add it to visual filter to judge if the item is selected.

Table = UNION('Item table','Refernce table')

Measure = IF(SELECTEDVALUE('union'[Item]) in VALUES('Item table'[Item])||SELECTEDVALUE('union'[Item]) in VALUES('Refernce table'[Item]),1,0)

Here's the result and the slicers are from two different fact tables.

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TonyBI]

Sorry to bother you again my friend, but I have another problem. The chart is working great, but when I try to replicate it with average values, it gives me the exact same chart... The idea is to have both charts, first one with all items an references selected as legend, and then another chart with the average of item selected and the average of refernces selected. Any idea of what could be wrong?

Thx in advance!

[TonyBI]

This is exactly what I was looking for, thx!!