cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors