Detect mismatch in Results associated with a Unique ID

[mmarchioni]

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?

[AlB]

@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 

[AlB]

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 

[mmarchioni]

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

[littlemojopuppy]

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

[littlemojopuppy]

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!  🙂

[mmarchioni]

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".