cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biengineer
Helper I
Helper I

Relationships Between Tables Wrong Results

Dear All,

 

I am unable to counter the problem related to Relationships between tables.

 

There are two Tables.

 

TableA
YearMontYearMonthArtTotal Gmts
2022-July2022JulyA1300
2022-July2022JulyB17680
2022-July2022JulyC2680
2022-July2022JulyD5485
2022-August2022AugustB10000
2022-August2022AugustC24920
2022-August2022AugustD2192
2022-August2022AugustE1781
2022-September2022SeptemberD2500
2022-September2022SeptemberE5185
2022-September2022SeptemberB6810
2022-September2022SeptemberC12820

 

TableB
YearMontYearMonthArtV.No.
2022-July2022JulyAA10
2022-July2022JulyBB10
2022-July2022JulyCA10
2022-July2022JulyDB10
2022-August2022AugustBA10
2022-August2022AugustCB10
2022-August2022AugustDA10
2022-August2022AugustEB10
2022-September2022SeptemberDA10
2022-September2022SeptemberEB10
2022-September2022SeptemberBA10
2022-September2022SeptemberCB10

 

 

    Using USERELATIONSHIP functionActive Relationship
    TableA (Art) to TableB(Art)TableA (YearMonth) to TableB(YearMonth)
YearMonthArtTotal GmtsCount of V.No.Count of V.No.1
2022JulyA1300163
2022JulyB17680363
2022JulyC2680563
2022JulyD5485763
2022AugustB10000397
2022AugustC24920597
2022AugustD2192797
2022AugustE17811197
2022SeptemberD2500758
2022SeptemberE51853458
2022SeptemberB6810358
2022SeptemberC12820558

 

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.

 

2 ACCEPTED SOLUTIONS
deevaker
Resolver I
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 

deevaker_0-1666128304158.png

 

Thanks,

Deevaker

+91-9711975011

deevaker@hotmail.com

https://www.linkedin.com/in/deevakerg/

View solution in original post

May be you would like to use EOmonth function (for end of month date). So that it takes end of month date only. 

View solution in original post

5 REPLIES 5
deevaker
Resolver I
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 

deevaker_0-1666128304158.png

 

Thanks,

Deevaker

+91-9711975011

deevaker@hotmail.com

https://www.linkedin.com/in/deevakerg/

Dear Sir,

 

This is perfect. 

 

biengineer_0-1666165981635.png

 

----------------------

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. 

 

biengineer_1-1666166101308.png

 

 

Though the results are fine when I filter the Date by Year and Monthly only in the first screenshot.

 

Best Regards.

 

 

May be you would like to use EOmonth function (for end of month date). So that it takes end of month date only. 

Dear 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.

Yes, I have shared the permission just now. Please try again now

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.