cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
falsumconsult
Regular Visitor

Identify Errors in Table 1 with reference data from Table 2

I am very new to data analysis and Power BI. So, please excuse me if this is an easy answer!

 

Background

I have payment data from a third-party with 1000 rows of payment data in 'Table 1'. Column C holds data with an 8-digit classification for the type of payment made.

 

'Table 2' is a small list of '8-digit' expected values for the type of payment made. 'Table 2, Col A' contains only 10 expected values.

 

When compared, any data point in 'Table 1, Col C' that is outside of the 'Table 2' reference data is deemed to be 'errata' with further investigation needed.

 

Request

I am looking to understand:

  1. How I can identify rows in 'Table 1' that are deemed to be 'errata'. 
    1. Would this mean pulling these 'errata' rows into a new table?
    2. Or, am I able to do this without generating new tables?
  2. Run a high-level metric which displays % of total payments with errata.

 

Any help & guidance would be greatly appreciated as I learn my way around Power BI and Data Analysis.

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @falsumconsult ,

 

You could create two measures to get the result.

Measure1 is to add marker.

Measure1 = IF(SELECTEDVALUE(table1[columnC]) in VALUES(table2[columnA]),"not errata","errata")

Measure2 is to calculate the percentage.

Measure2 = 
var _total = calculate(sum([value]),allselected('table1'))
var _errata = calculate(sum([value]),filter(allselected('table1'),Measure1="errata"))
return
_errata/_total

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi Jay,

This is great and a real help. The only issue I have is that measure 2 is returning a string value. Can you please advise on how I convert to a %?

 

Hi @falsumconsult ,

 

What's data type of the [values]? _total  and _errata sum of corresponding value. They should be number type.

Try modifying '_errata/_total' to divide(_errata,_total).

If you are saying the format, you could select the measure, go to Data View, modelling Tab, Change the data type to percentage.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
johnt75
Super User
Super User

Create a 1-to-many relationship from Table2 Column A to Table 1 Column C. Put the data columns you need from Table 1 into a table visual and apply a filter so that you only show values where Table 2 Column A is blank.

jaideepnema
Super User
Super User

Hi @falsumconsult ,

There are many ways of doing this. You can import both the tables into Power BI and create a relationship basis the 8 digit key which as per understanding will create a many to one relationship with the table 1 on the many side.  Then you can use DAX Measures to calculate the percentage of total payments with errata. You will get a blank value whenever a matching value is not found.

 

Alternatively you can merge these two tables into Power BI Query editor to get the required column into your 1st table and then write measures to the final calculation required. Hope this helps !!

 

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.