Power BI Duplicate rows appeared in relationship many-to-many

[Anonymous]

I have 3 tables added as source to a Power BI file.
The join relationship is below, with join on 'Person ID'.

Visual output :
John has 4 Visit dates and 2 Readings.
But the Readings for John, are repeated per visit dates (refer to green marking ). Same for person Mary. 

'Reading' rows repeated per visit date

The below Desire output needs to show the various visit dates and Reading taken per person ID, without the repeated visit date rows. Note 'Co latest visit' contains Dax formula in column.

Desire output

I guess this is related to the join relationship. Is there a way to get the desire output?
Please let me know if you need more information. Thanks in advance for your help!

Raw data
File 1 : Visit date

Person ID	Person Name	Visit dates
9341	John	17 Feb 2023
9341	John	21 Feb 2023
9341	John	28 Mar 2023
9341	John	9 Jun 2023
21342	Mary	22 May 2023
21342	Mary	25 Sep 2023
21342	Mary	24 Aug 2023

File 2 : Measurement

Person ID	Person Name	Reading	Date taken
9341	John	6.2	17-FEB-2023
9341	John	6.1	9-JUN-2023
21342	Mary	8.2	22 May 2023
21342	Mary	7	24 Aug 2023

File 3 : Profile

Person ID	Person Name	Height
9341	John	150
21342	Mary	170

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Measure:

Co latest visit = CALCULATE(MAX('Table 1'[Visit dates]),ALLEXCEPT('Table 1','Table 1'[Person ID]))

Measure = Var _Minvisitdate=CALCULATE(MIN('Table 1'[Visit dates]),ALLEXCEPT('Table 1','Table 1'[Person ID]))
Return
IF(SELECTEDVALUE('Table 1'[Visit dates])=_Minvisitdate,SUM('Table 2'[Reading]))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi v_zhangti

For your suggested MAX measures, they worked in my expected output. I accepted the solution.

My 3rd reply posted yesterday and 2 days ago ( with Q1 and Q2) , appeared today. I check with community and was informed that that reply was detected as spam due to different factors and hence it was not able to be displayed. 

As my raw data and expected output has some changes, so these questions no longer current. Please ignore them. If I have further question, I will post as new question that will be clearer. Thanks again for your help!

[Anonymous]

Hi v_zhangti

Below is the reply I post 4 times but did not appear in this question. I tried again.

Q1) I applied your suggested measure in my tables, but the result does not seem to be correct as the visit dates still repeat per person id. I suspected it is due to the Many -to -Many relationship (my Measurement to Visit tables ,which is your Table1 to Table2). 

What I tried to correct this issue, was to remove the duplicated rows of Person_ID ( with the different visit dates) from the Visit table;  only take the latest visit date and adjusted the Join relationship to be (Visit) One- to- Many’ -->(Measurement).

Additionally,

I added the raw data, ‘Reading2’ , Date_reading2 and removed one of Mary’s reading1 for checking. The below output looks alright.

Q2) One new question I like to check if this possible here is to find number of persons who completed the reading cycle.

*There are two Reading cycles : Reading1 and Reading2.

Person who took >=2 times of readings in each cycle, is considered to have completed 1 reading cycle.

Example:

John took two readings in cycle1 and two readings in cycle2.

Mary took only one reading in cycle 1 but two readings in cycle 2 .

Expected Answer:

Person ID	Person Name	Reading1 cycle	Reading2 cycle
9341	John	1	1
21342	Mary	0	1

How do I count the number of persons who has taken two times of reading in each cycle?

Attempted using Dax to create the Summarize table or a new measure did not work.

Summarize table:

Dax measure: Count persopn take > two times of 'reading1':

Dax measure: Count persopn take > two times of 'reading1':

I could not find the setting to allow me to attach my Power Bi file. I added google link to my PBI and raw data files here.

My PBI in Google drive 

In case you have issue access the link,  please use the UPDATED raw data  'Measurement'table. Please let me know how can I attach my Power BI file if I need some special method.

Alternatively, the raw data Measurement table.

Person ID	Person Name	Reading1	Date Reading1 taken	Reading2	Date Reading2 taken
9341	John	6.2	17 Feb 2020	8.0	17 Feb 2020
9341	John	6.1	9 Jun 2020	9.0	9 Jun 2020
21342	Mary	8.2	22 May 2020	10.0	22 May 2020
21342	Mary			11.0	25 Sep 2020

Please let me know if you have found a possible solution for Q2.

Thanks in advance!

[Anonymous]

Hi v-zhangti

Thanks for your help. I tried to reply with my data 4 times but my reply did not get saved. If it is still an issue by tomorrow, I will raise it as a new question.

[Anonymous]

Hi v-zhangti,

Please close this query as I still cannot save my post with screenshots and my google link for the files. I will raise it a new query. Thank youso much for your help!

[Anonymous]

Hi v_zhangti

Thanks so much for working on my question.

Q1) I applied your suggested measure in my tables, but the result does not seem to be correct as the visit dates still repeat per person id. I suspected it is due to the Many-to-Many relationship ( Measurement – to – Visit) which is your Table1 to Table2.

What I tried to correct it was to remove the duplicated rows of Person_ID ( with the different visit dates) from the Visit table;  only take the latest visit date and adjusted the Join relationship to be (Visit) One- to- Many’ -->(Measurement).

Additionally,

I added the raw data, ‘Reading2’ , Date_reading2 and removed one of Mary’s reading1 for checking the result. The below output looks alright.

Q2) One new question I like to check if this possible here is to find number of persons who completed the reading cycle.

*There are two Reading cycles : Reading1 and Reading2.

Person who took >=2 times of readings in each cycle, is considered to have completed 1 reading cycle.

Example:

John took two readings in cycle1 and two readings in cycle2.

Mary took only one reading in cycle 1 but two readings in cycle 2 .

Expected Answer:

Person ID	Person Name	Reading1 cycle	Reading2 cycle
9341	John	1	1
21342	Mary	0	1

How do I count the number of persons who has taken two times of reading in each cycle?

Attempted using Dax to create the Summarize table or a new measure did not work.

I could not find the setting to attach my Power Bi file. Here are my PBI and raw data in My Google drive . I tried to add my google drive which has my PBI files but fail to save my reply.  

Alternatively, raw data of Measurement table is below.

Person ID	Person Name	Reading1	Date Reading1 taken	Reading2	Date Reading2 taken
9341	John	6.2	17 Feb 2020	8.0	17 Feb 2020
9341	John	6.1	9 Jun 2020	9.0	9 Jun 2020
21342	Mary	8.2	22 May 2020	10.0	22 May 2020
21342	Mary			11.0	25 Sep 2020

Please let me know if you have found a possible solution. Thanks in advance!

[AnalyticPulse]

try using stepped layout in you matrix, or create a filter measure to solve this.

[Anonymous]

Thanks for your recommendation. 
I tried the visual matrix and the Reading appear repeatedly under all 4 visit dates. For John, Reading are taken on 2 visit dates 17 Feb and 9 Jun, so they should only appear on the 2 dates.

I am not sure how to filter using measure as I am pretty new to Power Bi. Appreciate if you could you provide some guide on the possible solution. Thanks in advance!

[AnalyticPulse]

its difficult to debug without the data and powerbi file, cant say what would be the issue