Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
Solved! Go to 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. |
@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. |
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. |
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 !
@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. |
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
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.
Thank you for your answer, I'll try that too !
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |