topic Re: Detect mismatch in Results associated with a Unique ID in DAX Commands and Tips

Detect mismatch in Results associated with a Unique ID

mmarchioni — Fri, 08 Jan 2021 19:06:08 GMT

I would like to be able to show only the Unique ID rows with mismatched Results. Example:

Unique ID	Test Type	Result	Test Date
123	Abby1	A	1/1/2020
123	Abby2	A	1/10/2020
123	Abby1	B	2/1/2020
123	Abby1	A	2/5/2020
456	Abby2	B	1/8/2020
456	Abby1	B	2/10/2020
456	Abby1	B	2/28/2020
789	Abby1	A	1/1/2020
789	Abby1	O	3/5/2020

Rows for 123 and rows for 789 would show because all of the results associated with their unique ID are not matching.

Rows for 456 would not show because all the results for 456 match.

Any suggestions?

Re: Detect mismatch in Results associated with a Unique ID

littlemojopuppy — Fri, 08 Jan 2021 19:21:00 GMT

Hi mmarchioni

You can create a calculated column like this

All Tests Match = VAR TestSummary = GROUPBY( FILTER( ALL(Tests), Tests[Unique ID] = SELECTEDVALUE(Tests[Unique ID]) ), Tests[Unique ID], Tests[Result], "RowCount", COUNTX( CURRENTGROUP(), Tests[Unique ID] ) ) VAR RecordCount = COUNTROWS(TestSummary) RETURN IF( RecordCount > 1, FALSE(), TRUE() )

Then when you create a visualization on the canvas, you can filter it for where the calculated column = FALSE()

Hope this helps! 🙂

Re: Detect mismatch in Results associated with a Unique ID

AlB — Fri, 08 Jan 2021 19:24:56 GMT

Hi mmarchioni

With what you show as visual, create a measure:

Show measure = VAR check_ = CALCULATE ( DISTINCTCOUNT ( Table1[Result] ), ALLEXCEPT ( Table1, Table1[Unique ID] ) ) > 1 RETURN IF ( check_, 1, 0 )

Then use the measure as a filter for the visual, choosing to show when the value is 1

Please mark the question solved 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.

Cheers

Re: Detect mismatch in Results associated with a Unique ID

littlemojopuppy — Fri, 08 Jan 2021 19:26:19 GMT

mmarchioni ...go with this. Far more elegant than my solution! 🙂

Re: Detect mismatch in Results associated with a Unique ID

mmarchioni — Mon, 11 Jan 2021 13:30:24 GMT

I tried this and the only answer I get is True (even when it is false). It isn't that the Result field has to have a result, the Result field results must all match for each Unique ID for it to be "true".

Re: Detect mismatch in Results associated with a Unique ID

mmarchioni — Mon, 11 Jan 2021 13:38:49 GMT

I tried this as well. Everything shows a value as 1.

Re: Detect mismatch in Results associated with a Unique ID

AlB — Mon, 11 Jan 2021 14:04:25 GMT

mmarchioni

You're probably doing something differently. See it at work in the attached file.

Please mark the question solved 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.

Cheers