Identify row counts greater than 1

[ARatz]

Hi

I hava the followign Dimensions: Deals (with the Deal ID), Offering, Profit Center and two Fact Tables with one measure from each. I want to identify all Deal ID with show up in the visual more than once due to not matching Offerings in the two fact tables. fe. Deal ID 10509130458 should show up as 2 since I have two rows caused by differnt Offerings. 

See extract in file attachment

https://swissbusinessanalyticscom.sharepoint.com/:x:/s/adnovum/EbHeKrZOACZDpIzVd3IdH8kBXQWA3T-H3nWFW... 

best

Andreas

[v-nmadadi-msft · Accepted solution]

Hi @ARatz  ,
Thanks for reaching out to the Microsoft fabric community forum.

To identify all Deal IDs that appear more than once in a Power BI visual due to mismatched Offering values across two different fact tables, you can create a unified structure that allows for easy comparison. You can create a new DAX table that combines the Deal ID and Offering columns from both fact tables using the UNION function. This table consolidates all Offering entries tied to each Deal ID from both sources.
UNION (DAX) FUNCTION - DAX | Microsoft Learn

Combine multiple tables with UNION / UNION ALL in ... - Microsoft Fabric Community

Once this combined table is created, define a DAX measure that counts the distinct number of Offering values per Deal ID using Distinct count
DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

You could use all except to filter out the unrequired data in this scenario.
ALLEXCEPT function (DAX) - DAX | Microsoft Learn



Next, use a Power BI table visual to display the Deal ID alongside this new measure. Apply a visual-level filter to highlight only those records where the Offering Count Per Deal is greater than 1.

This approach effectively reveals all Deal IDs that have conflicting or multiple offerings due to inconsistencies between the two systems.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[ARatz]

H Tom

Thx for your quick response. Since these are customer data I cannot share the pbix. does it help when i share you the data model?

[TomMartens]

Hey @ARatz ,

please create a pbix that contains sample data but still reflects the semantic model.
Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link to the file.

Regards,
Tom