Forum Discussion
Helper IRelationships Between Tables Wrong Results
Dear All,
I am unable to counter the problem related to Relationships between tables.
There are two Tables.
| TableA | ||||
| YearMont | Year | Month | Art | Total Gmts |
| 2022-July | 2022 | July | A | 1300 |
| 2022-July | 2022 | July | B | 17680 |
| 2022-July | 2022 | July | C | 2680 |
| 2022-July | 2022 | July | D | 5485 |
| 2022-August | 2022 | August | B | 10000 |
| 2022-August | 2022 | August | C | 24920 |
| 2022-August | 2022 | August | D | 2192 |
| 2022-August | 2022 | August | E | 1781 |
| 2022-September | 2022 | September | D | 2500 |
| 2022-September | 2022 | September | E | 5185 |
| 2022-September | 2022 | September | B | 6810 |
| 2022-September | 2022 | September | C | 12820 |
| TableB | ||||
| YearMont | Year | Month | Art | V.No. |
| 2022-July | 2022 | July | A | A10 |
| 2022-July | 2022 | July | B | B10 |
| 2022-July | 2022 | July | C | A10 |
| 2022-July | 2022 | July | D | B10 |
| 2022-August | 2022 | August | B | A10 |
| 2022-August | 2022 | August | C | B10 |
| 2022-August | 2022 | August | D | A10 |
| 2022-August | 2022 | August | E | B10 |
| 2022-September | 2022 | September | D | A10 |
| 2022-September | 2022 | September | E | B10 |
| 2022-September | 2022 | September | B | A10 |
| 2022-September | 2022 | September | C | B10 |
| Using USERELATIONSHIP function | Active Relationship | ||||
| TableA (Art) to TableB(Art) | TableA (YearMonth) to TableB(YearMonth) | ||||
| Year | Month | Art | Total Gmts | Count of V.No. | Count of V.No.1 |
| 2022 | July | A | 1300 | 1 | 63 |
| 2022 | July | B | 17680 | 3 | 63 |
| 2022 | July | C | 2680 | 5 | 63 |
| 2022 | July | D | 5485 | 7 | 63 |
| 2022 | August | B | 10000 | 3 | 97 |
| 2022 | August | C | 24920 | 5 | 97 |
| 2022 | August | D | 2192 | 7 | 97 |
| 2022 | August | E | 1781 | 11 | 97 |
| 2022 | September | D | 2500 | 7 | 58 |
| 2022 | September | E | 5185 | 34 | 58 |
| 2022 | September | B | 6810 | 3 | 58 |
| 2022 | September | C | 12820 | 5 | 58 |
Problem: When I use Art to Art relationship with Many-Many Wherever the Art B exists in any month, it is counted and displayed in every month.
Problem: And when I concatenate the Month and Year, and use that of Relationship, it counts total of the month and display infront of every Art.
Best Regards.
Hi biengineer ,
You can create 2 dim tables where you can save art and monthyear data in each table. and then build relation with both TableA and TableB - Download pbix here
Thanks,
Deevaker
+91-9711975011
May be you would like to use EOmonth function (for end of month date). So that it takes end of month date only.
5 Replies
- deevaker
Resolver I
Hi biengineer ,
You can create 2 dim tables where you can save art and monthyear data in each table. and then build relation with both TableA and TableB - Download pbix here
Thanks,
Deevaker
+91-9711975011
biengineerDear Sir, Thanks for your help.
I am applying the way you have directed, please can you accept my request to access the Download pbix here file. It requires permission.
Regards.
- deevaker
Yes, I have shared the permission just now. Please try again now
- biengineer
Dear Sir,
This is perfect.
----------------------
I am using this function to extract month and year from the date
YearMonth = Format('Export'[DATE:], "MMM YY")And it creates multiple Month-Year with different day. There should be same day for all rows as I am extracting Month and Year only.
Though the results are fine when I filter the Date by Year and Monthly only in the first screenshot.
Best Regards.
- deevaker
May be you would like to use EOmonth function (for end of month date). So that it takes end of month date only.