Getting a data quality score of accepted values from a table

[AhmishCyborg]

Hi All,

Does anyone know a quick way of displaying quality scores of how many returns in a table have acceptable values and how many have nulls?

I have 2 tables one of returns and one of acceptable values (the returns table is fairly big 100 or so columns and 700K rows) see below for tables and desired outcome.

My current solution is to create a measure for each column with if statements that counts all the rows where the returns have acceptable values and then break down into nulls and acceptable values and percentages with further measures. However, the returns table has a lot of columns and sometimes a lot of acceptable values and this will take a lot of time. Is there a way to match the returns table to the accepted values table and quickly get the desired output?

I do not mind if the solution is in power query or measures etc.

Thanks

[Anonymous · Accepted solution]

Hi @AhmishCyborg 

Based on your needs, I have created the following form.

You can use the following dax to get the result you want.

CommonValuesInCol1 = 
VAR accept_number=
COUNTROWS(
    INTERSECT(
        DISTINCT('Table'[Col1]),
        DISTINCT('Table (2)'[Col1 accept value])
    )
)
RETURN
DIVIDE(accept_number,COUNTROWS('Table'))

CommonValuesInCol2 = 
VAR accept_number=
COUNTROWS(
    INTERSECT(
        DISTINCT('Table'[Col2]),
        DISTINCT('Table (2)'[Col2 accept value])
    )
)
RETURN
DIVIDE(accept_number,COUNTROWS('Table'))

Best Regards,

Jayleny

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