cancel
Showing results for
Did you mean:
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 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.

2 ACCEPTED SOLUTIONS
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

deevaker@hotmail.com

Resolver I

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 5
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

deevaker@hotmail.com

Helper I

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.

Resolver I

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

Helper I

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.

Resolver I

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

Announcements

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

#### 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!

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors