Finding missing record

DAX DAX Calculations
1

The AR data sets created by a software program has numerous mathematical issues. Negative values & values multiplying themselves each time a claim is resubmitted.
I have figured out a way to use the visit data to calculate the correct gross billable claim number and then join this data to the open AR claim numbers and create an ‘aging’ report that shows a more accurate gross AR number.

My biggest concern was whether there was any possibility that join would miss records as this software is so poorly designed.

I did a count and a distinct count on the data and found there is a 1 record in AR but not in visit datal.
I extracted the results and did a vlookup to figure out which claim is missing in the visit data (Claim #9571 is the issue)

Which leads me to wonder is there a way to create a DAX measure that would highlight any mismatches of the extracted data w/o having to bring data into excel as I expect there will be other mismatches downstream. The detail data has many records that are ‘closed’ and no longer relevant to AR calculations which messes me up when trying to think through an ‘if’ statement assuming that’s the best path but I suspect there’s a much cleaner way to get this data.
AR Test File.xlsx (68.5 KB)
DetailTestData.xlsx (2.3 MB)

In short for this example I want to have a visual that would show ‘claim 9571’ as the data mismatch.

Excel data & PBIX attached.

Thanks in advance.

AR Adjustment Test.pbix (695.5 KB)

2

Hello, @gkavesh

As for me the best way to work around such issues is Power Query.
So, kindly be aware, to change data sources back to your initial file location in Power Query, Query Parameters, Source step.
So, please find in attach your file with missed and identified #9571 Claim, which is in AR Test File, but not in DetailTestData.

What was done step by step:

  1. Duplicate AR Claims Request in PQ
  2. Merge queries use LeftOuter and Expand Detailed data
    – on this step if in Column Detailed Data.Claim Number you’ll see Null value - it’s mean that record is in AR Claims, but not in DetailedData.
    image
    image1243×210 17.3 KB

    in case you’d like to find more information about merging queries: https://portal.enterprisedna.co/courses/197328/lectures/3694417
  3. So, just filter this column to have only NULL values and then delete all columns except original one - “Promt Claim #”
  4. Load data, add new table to model, count distinct values in it

image
image1045×158 7.17 KB

Hope, it helps you!

BR,
Oleg
AR Adjustment Test.pbix (706.3 KB)

2 Likes
3

Hi @gkavesh

If you are looking for DAX solution, we can find missing record using either Calculated Column or Measure.

Here is the solution using Calculated Column.

Create calculated column for AR Claims like below.

image
image1204×389 17.6 KB

image

AR Adjustment Test.pbix (696.2 KB)

2 Likes
4

Thank you both solutions worked and I’ll likely go the DAX option so I remember where the data is coming from.

gina