Hiding Matrix values by adding a flag to the source data.

[RichXM655]

I’m trying to create a column that will flag a row as being included, or not, for display.

I need basically to hide any rows where the number of unique respondents in a subgroup who gave that particular response to that particular question is one or less.

The data is unpivoted, so each row of the source table is one question response from one respondent.

So – the code I’m trying to write is attempting to do a filtered calculate where it counts the distinct number of respondents (each has a unique ID, “UserNo”), where these people meet these critera:

1:They have given the same answer (“1-5 num” – just a number between 1 and 5)

2: They have answered the same question (“QCode” – Text string)

3: They are all in the same organisation (“Org Name (Display)” - Text string)

4: They are all Training in the same subject (“Training Subject” - Text string)

then, if this count comes to more than one, return “true”, otherwise, “false”

This is the code I’ve got:

ShowResponse =

IF(CALCULATE(DISTINCTCOUNT

('Response Data'[UserNo]),

    'Response Data'[1-5 num]='Response Data'[1-5 num],

        (IF('Response Data'[Org Name (Display)] = 'Response Data'[Org Name (Display)],"TRUE","FALSE")),

                (IF('Response Data'[QCode] = 'Response Data'[QCode],"TRUE","FALSE")),

                    (IF('Response Data'[Training Subject]='Response Data'[Training Subject],"TRUE","FALSE"))

                ) > 1,"TRUE","FALSE")

It runs, but it comes back with “FALSE”, for every row. I think I know why (the DISTINCTCOUNT one comes up with 1 because it's comparing to itself, then you can’t subfilter lower than 1…) so I need a different approach. Basically, how to calculate, for each row, the number of other rows in the same table that are similar to it based on the above criteria. Is that even possible, or is it considered circular and therefore impossible?

Basically, the desired end result is a matrix that needs to act as if these rows aren’t there at all when it calculates each cell. I’ve tried applying filters to the visual (based on a creating a measure doing distinct count of UserNo) but that doesn’t work, so I’m trying to flag the data upstream, if that makes sense.

[v-shex-msft]

Hi @RichXM655,

It seems like you want to use current contents value to find out the similar value in your table, right?
If this is a case, I'd like to suggest to use the EARLIER function to extract current value and concatenate these conditions with OR function or operator '||':

ShowResponse =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Response Data'[UserNo] ),
        FILTER (
            'Response Data',
            'Response Data'[1-5 num] = EARLIER ( 'Response Data'[1-5 num] )
        )
    ) > 1
        || CALCULATE (
            DISTINCTCOUNT ( 'Response Data'[UserNo] ),
            FILTER (
                'Response Data',
                'Response Data'[Org Name (Display)]
                    = EARLIER ( 'Response Data'[Org Name (Display)] )
            )
        ) > 1
        || CALCULATE (
            DISTINCTCOUNT ( 'Response Data'[UserNo] ),
            FILTER (
                'Response Data',
                'Response Data'[QCode] = EARLIER ( 'Response Data'[QCode] )
            )
        ) > 1
        || CALCULATE (
            DISTINCTCOUNT ( 'Response Data'[UserNo] ),
            FILTER (
                'Response Data',
                'Response Data'[Training Subject]
                    = EARLIER ( 'Response Data'[Training Subject] )
            )
        ) > 1,
    "TRUE",
    "FALSE"
)

DAX EARLIER function 

If above not help, please share some dummy data with a simlar data structure to test.

Regards,

Xiaoxin Sheng

[RichXM655]

Thanks for that.

That runs, but doesn't give me the result I;m looking for.

ShowResponse =
IF (
Calculate
(
the number of distinct values of [UserNo] in all rows,
FILTERED TO
with the same value of [num 1-5] that this row has,
AND
with the same value of [org name (display)] that this row has,
AND
with the same value of [Qcode] that this row has,
AND
with the same value of [Training Subject] that this row has
)
)
> 1,
"TRUE",
"FALSE"
)

Here's some dummy data, with the expected result. (I;ve already filtered down to "Category", "Training Subject", and "Org Name (Display)" for the sake of space but the code needs to check that these match the row we're generating a value for).

UserNo	QCode	Answer	Category	Training Subject	Org Name (Display)	1-5 num	EXPECTED RESULT
21327	Q22.1	Never	B	AC	AI	5	TRUE
7647	Q22.1	Never	B	AC	AI	5	TRUE
2851	Q22.1	Once or twice	B	AC	AI	4	FALSE
10548	Q22.1	Occasionally	B	AC	AI	2	FALSE
21327	Q22.2	Never	B	AC	AI	5	TRUE
7647	Q22.2	Never	B	AC	AI	5	TRUE
2851	Q22.2	Once or twice	B	AC	AI	4	FALSE
10548	Q22.2	Occasionally	B	AC	AI	2	FALSE
21327	Q22.3	Never	B	AC	AI	5	FALSE
7647	Q22.3	Once or twice	B	AC	AI	4	TRUE
2851	Q22.3	Once or twice	B	AC	AI	4	TRUE
10548	Q22.3	Occasionally	B	AC	AI	2	FALSE
21327	Q25.1	Yes	B	AC	AI	5	TRUE
10548	Q25.1	Yes	B	AC	AI	5	TRUE
7647	Q25.1	Yes	B	AC	AI	5	TRUE
2851	Q25.1	Yes	B	AC	AI	5	TRUE

So, ordered by question, we return "TRUE" when more than one value of UserNo has given a particlar response where the QCode, Training Subject and Org Name(Display) Match. So, for 22.1, 2 people said "5", so those rows get "true", but only one each for "2" and "4", so those get "false". and so on down the list.

[v-shex-msft · Accepted solution]

Hi @RichXM655,

So you mean you want to check if it existed row that suitable for all conditions? If this is a case, you can refer to the following calculation column formula:

ShowResponse =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Response Data'[UserNo] ),
        FILTER (
            'Response Data',
            [1-5 num] = EARLIER ( 'Response Data'[1-5 num] )
                && [Org Name (Display)] = EARLIER ( 'Response Data'[Org Name (Display)] )
                && [QCode] = EARLIER ( 'Response Data'[QCode] )
                && [Training Subject] = EARLIER ( 'Response Data'[Training Subject] )
        )
    ) > 1,
    "TRUE",
    "FALSE"
)

Regards,

Xiaoxin Sheng

[RichXM655]

Yeah, Overnight I came up with a similar solution using COUNTROWS which gave the same result.

Thanks 🙂