Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a dataset below:
Location | Name | age | income |
UK | x | teen | low |
UK | x | adult | low |
UK | y | teen | high |
USA | z | adult | low |
USA | z | adult | high |
I want to point out mistakes in the dataset where for one person ( ie x or z), the age or income columns are not the same (when they should be)
I want to end up with a table visual in powerbi whereby it points out the discrepancy
Location | Name | Discrepancy found? |
UK | X | Y |
UK | Y | N |
USA | Z | Y |
What dax can i use to do this?
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here's my solution. Create a measure.
Discrepancy found? =
IF (
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[Location] = MAX ( 'Table'[Location] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
&& (
'Table'[age] <> MAX ( 'Table'[age] )
|| 'Table'[income] <> MAX ( 'Table'[income] )
)
)
) > 0,
"Yes",
"No"
)
Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here's my solution. Create a measure.
Discrepancy found? =
IF (
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[Location] = MAX ( 'Table'[Location] )
&& 'Table'[Name] = MAX ( 'Table'[Name] )
&& (
'Table'[age] <> MAX ( 'Table'[age] )
|| 'Table'[income] <> MAX ( 'Table'[income] )
)
)
) > 0,
"Yes",
"No"
)
Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
Create a Calculated Column as shown below...
Discrepancy =
IF (
CALCULATE (
COUNT ( 'Table'[income] ),
FILTER (
'Table',
'Table'[Location] = EARLIER ( 'Table'[Location] )
&& 'Table'[Name] = EARLIER ( 'Table'[Name] )
)
) > 1,
IF (
DISTINCTCOUNT ( 'Table'[age] ) > 1
|| DISTINCTCOUNT ( 'Table'[income] ) > 1,
"Y"
),
"N"
)
Hi ddpl,
Thanks for submitting this, unfortunately it doesn't work as it doesn't fulfil the requirement of being able to indicate which ones have the same location & name, but different values for income or age.
If it helps, there will only ever be "teen" or "adult" values for age, and with Income it will only be "High", Low" or "N/a"
@Anonymous ,
can you share slightly more data with expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |