Forum Discussion
Comparing two fact tables
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.
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!!
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.
This is exactly what I was looking for, thx!!
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!