IF Measure causing duplicates

[JSmith912]

Hi,

I'm seeking advice on an issue I'm encountering. Unfortunately, I can't provide exact screenshots due to confidentiality reasons, so I'll do my best to describe the situation clearly. At our company, we use two applications to manage information about our equipment. It's crucial for both applications to consistently display identical information, requiring regular reconciliation. Each piece of equipment is assigned a unique ID number.

To facilitate this, I've created a table listing these ID numbers, with relationships to data extracts from both applications. The table includes columns for equipment model numbers from each application. Initially, everything displays as intended: the table shows the ID number, followed by the model number from each application.

However, when I attempt to add a simple conditional measure to compare these model numbers, shown below:

Master_Model_Check =
IF (
SELECTEDVALUE('MASTER DATA'[Model]) = SELECTEDVALUE('Oars'[Model]),
"Yes",
"Check"

Dragging this measure into the table unexpectedly causes the ID column to generate multiple duplicate entries, despite the absence of duplicate entries in either application's extracts. It seems to be associating IDs that do not match.

Could anyone provide insight or advice on how to resolve this issue?

Thank you!

[ajohnso2 · Accepted solution]

Is the relationship in your model Fact to Fact? i.e possibly many to many relationship?

If so consider creating a bridging table so you can insure integrity.

I.e create a table with all ID's from Table A and all ID's from Table B (ensure you remove duplicates). Your relationships can then be configured to join to the bridge table, both of your tables will have a 1 side (to the bridge table) and a many side on your fact tables.

[v-huijiey-msft]

Hi @JSmith912 ,

Did our answer help you?

If there is an answer that helped you, please accept it as a solution. This will be of great help to other users who have similar problems as you.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft · Accepted solution]

Hi @JSmith912 ,

Thanks for the reply from ajohnso2 / MFelix .

So, you now have two tables with ID numbers and relationships to the data extracts from the two applications. Am I understanding this correctly?

Make sure the relationships between the tables are set up correctly. Specifically, check that the relationships are based on unique ID numbers and that they are set up as one-way. This helps ensure that Power BI interprets the data model correctly.

You can create an ID table using:

ID Table = VALUES("Table"[ID])

This syntax will create an ID table with no duplicate values.

Relate this table to the two Fact tables, which should now have a propagation relationship of Single.

Try it again under this condition to see if it works.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ajohnso2 · Accepted solution]

Is the relationship in your model Fact to Fact? i.e possibly many to many relationship?

If so consider creating a bridging table so you can insure integrity.

I.e create a table with all ID's from Table A and all ID's from Table B (ensure you remove duplicates). Your relationships can then be configured to join to the bridge table, both of your tables will have a 1 side (to the bridge table) and a many side on your fact tables.

[MFelix]

Hi @JSmith912 ,

Without andditional information about the model and the way it's setup alongside with the visualization will be difficult to pinpoint the error. What I can rthink is for you to check if you are using the ID from the dimension table on your visualization and what is the aggregation for the model for both tables in the visualization.