Forum Discussion
Simple but not able to resolve
Dear Friends,
I have more than 2 tables, but for demonstration purpose. I m taking only 2 table
Table 1 : Has Sales,
| Month | Day | Region | No of Sales |
| 6 | 2 | India | 161 |
| 6 | 18 | India | 167 |
| 5 | 21 | India | 113 |
| 6 | 1 | India | 178 |
| 6 | 2 | India | 156 |
| 6 | 2 | India | 149 |
| 5 | 24 | India | 171 |
| 5 | 27 | India | 149 |
| 6 | 25 | India | 114 |
| 6 | 2 | India | 196 |
| 6 | 25 | India | 158 |
| 5 | 4 | India | 105 |
| 6 | 27 | India | 124 |
Table 2 : Has Order.
| Month | Day | Region | No of order |
| 6 | 20 | India | 4 |
| 6 | 27 | India | 8 |
| 5 | 16 | India | 1 |
| 6 | 30 | India | 10 |
| 6 | 7 | India | 6 |
| 6 | 21 | India | 4 |
| 5 | 2 | India | 8 |
| 5 | 20 | India | 5 |
| 6 | 27 | India | 6 |
| 6 | 16 | India | 8 |
| 6 | 21 | India | 6 |
| 5 | 2 | India | 5 |
| 6 | 3 | India | 6 |
Both tables has DAY, MONTH and records,
I want to have a matrix which can give me below table . On top of this, I would like to have MONTH slicer. (here I have a problem , whenever I took month slicer its giving wrong numbers ).
In a nut shell, I need below table.
Hi AbdurRahman
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi AbdurRahman
You can try my way to build a calendar table and relate all data tables with calendar table by Key column. You need a key column in all tables to build relationships. Here I use Month *100 +Day as Key table.
Calendar Table:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2021,01,01),DATE(2021,06,30)),"Month",MONTH([Date]),"Day",DAY([Date]),"Key",MONTH([Date])*100+DAY([Date]))Key in Has Order Table.
Key Order = 'Has Order'[Month]*100+'Has Order'[Day]Key in Has Sales Table.
Relationship is as below.
Build a Matrix table as below.
We need to turn on Show on row function in Values.
Use Month column in calendar table to build a slicer. Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear RicoZhou,
Thank you so much for solution with detailed explanation, Let me implement this solution with real-time then I will get back to you If i have any obstacles.
Thank you
A
Hi AbdurRahman
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It is Just an idea, because at least you could do using Power Query before get into PwBI, but also you cand do this using DAX,
what I'll do is change the name of the value column for something like "Value", I'll add an additional column called number of an there I'll include the description like, No of Sales or No of Order, once the tables are aligned I'll do an UNION on DAX for both tables (It works with as many tables as you need) once the UNION table has been created I'll put as you need to see.I build the example but I can not load 😄 hope it works,
Table Has SalesTable Has OrderTable UnionTable VisualizationThank you for you reply. But in real time, I have a huge file. more than one lakh records each file consist so putting them altogether impact performance. However, Now all of them individual files but I moving them all in database and planning to have DIRECT QUERY.
Could you help me now.
