Highlight logical errors in a dataset

[Anonymous]

Hi ! 

I wanted to know if there is a way in Power BI to highlight what a would call  a " Logical error " (I'm not sure if the word is correct). 

Let me explain, if I have a table setted like this : 

Fruit	Provenance	Quantity
Apple	Spain	2 tons
...	...	....

And I know that the Apples can't come from Spain, they must come from Italy, is there a way to highlight the concerned row to let me know that there is a mistake ? I'm talking about Power BI but if you guys have the solution on Excel it would be great too ! 

Thank you for you answers ! 

[AlB · Accepted solution]

@Anonymous 

Sorry, you need CALCULATETABLE rather than CALCULATE, since we are returning a table now

isCorrectProvenance = 
VAR correctProvenance_ =
    CALCULATETABLE (
        DISTINCT ( Table2[Provenance] ),
        TREATAS ( DISTINCT ( Table1[Fruit] ), Table2[Fruit] )
    )
VAR provenance_ = SELECTEDVALUE ( Table1[Provenance] )
RETURN
    IF ( provenance_ IN correctProvenance_, 1, 0 )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @Anonymous 

One option is to create a measures that tells you whether the provenance is correct:

isCorrectProvenance = 
VAR correctProvenance_ =
    CALCULATE (
        DISTINCT ( Table2[Provenance] ),
        TREATAS ( DISTINCT ( Table1[Fruit] ), Table2[Fruit] )
    )
VAR provenance_ = SELECTEDVALUE ( Table1[Provenance] )
RETURN
    IF ( provenance_ = correctProvenance_, 1, 0 )

 and then use that to highlight the rows where the result is 0. You can do that by applying conditional formatting to all the fields shown in the table visual. Note you need an additional table with the correct provenance per fruit, of course.

See it all at work in the attached file.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Thank you for your Answer it works great with the apples ! But when I tried to complexify the table with some new data :  

Table 1 : 

Table 2 : 

I have an error saying : 

'Table1[Iscorrectprovenance] : A table of multiple values was provided when a single value was expected

Any idea how to perform that ? Thank you very much for your time ! 

[AlB]

@Anonymous 

Sure that is because you have more than one provenance for apple for instance. So the

DISTINCT ( Table2[Provenance] ) returns a two-rows, one-column table:

Italy

China

and that makes the comparison

provenance_ = correctProvenance_

fail, since the = requires scalars on both sides, not tables.

If the intention is that the provenance in Table1 is one of those in Table2 for that fruit, you can do a minor change IN  instead of = for that comparison

isCorrectProvenance = 
VAR correctProvenance_ =
    CALCULATE (
        DISTINCT ( Table2[Provenance] ),
        TREATAS ( DISTINCT ( Table1[Fruit] ), Table2[Fruit] )
    )
VAR provenance_ = SELECTEDVALUE ( Table1[Provenance] )
RETURN
    IF ( provenance_ IN correctProvenance_, 1, 0 )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Thanks a lot ! Last time i'm bothering you, I tried your solution but I have the following message 

'the function expects a table expression for argument "", but a string or numeric expression was used.'

And I can't get around it, if you have any solution I would be very grateful ! Have a nice day

[gdarakji]

You can create a calculated column in which you perform all these checks and then set it to true or false depending on whether the data is correct or not. For example, in the case mentioned above, you can do this with excel:

=IF(AND([Provenance]<>"Italy",[Fruit]="Apple"),FALSE,TRUE)

After that, you can create a conditional formatting rule to highlight all rows having this new column = FALSE.

The above formula is for illustration purposes only. You can add all of your validation conditions to it by having multiple ANDs.

[Anonymous]

Thank you for your answer, I'll try that too !