Count Rows with XX number of Columns Out

[antolope]

Hey All,

First time posting and total newbie. I'm trying to write a measure that would count a row only if X number of columns are marked out. In the below example, I have a table with 24 cloumns. I would like to count any row with more than 5 of the 24 columns marked "OUT" and place that number in a card visual:

I tried using a switch statement to convert all the "OUT" to a number 1 and then SUM all the 1s to get a count like so:

FILTER (
'QA Review Item Pivot',
IF (
SWITCH(
TRUE(),
'QA Review Item Pivot'[11] = "OUT", 1, 0, +
'QA Review Item Pivot'[12] = "OUT", 1, 0, +
'QA Review Item Pivot'[13] = "OUT", 1, 0, +
'QA Review Item Pivot'[14] = "OUT", 1, 0, +
'QA Review Item Pivot'[15] = "OUT", 1, 0, +
'QA Review Item Pivot'[16] = "OUT", 1, 0, +
'QA Review Item Pivot'[17] = "OUT", 1, 0, +
'QA Review Item Pivot'[18] = "OUT", 1, 0, +
'QA Review Item Pivot'[19] = "OUT", 1, 0, +
'QA Review Item Pivot'[20] = "OUT", 1, 0, +
'QA Review Item Pivot'[21] = "OUT", 1, 0, +
'QA Review Item Pivot'[22] = "OUT", 1, 0, +
'QA Review Item Pivot'[23] = "OUT", 1, 0, +
'QA Review Item Pivot'[24] = "OUT", 1, 0, +
'QA Review Item Pivot'[25] = "OUT", 1, 0, +
'QA Review Item Pivot'[26] = "OUT", 1, 0, +
'QA Review Item Pivot'[27] = "OUT", 1, 0, +
'QA Review Item Pivot'[28] = "OUT", 1, 0, +
'QA Review Item Pivot'[29] = "OUT", 1, 0, +
'QA Review Item Pivot'[30] = "OUT", 1, 0, +
'QA Review Item Pivot'[31] = "OUT", 1, 0, +
'QA Review Item Pivot'[32] = "OUT", 1, 0, +
'QA Review Item Pivot'[33] = "OUT", 1, 0, +
'QA Review Item Pivot'[34] = "OUT", 1, 0
) > 4

but PowerBI would object with a "convert using FORMAT or VALUE" error. Tried converting using FORMAT and VALUE and still nothing.

I can't do && / || cause that would count anything with either ALL of them OUT or ANY one of them OUT. 

Tried stuffing things into variables and still got the FORMAT or VALUE error. At this point I'm reaching out for help. There's got to be a simple way I'm totally missing. Any assistance would be greatly appreciated! Thanks

[antolope · Accepted solution]

If any one stumbles upon this, in the end, I was forced to go with calculated columns created by the DBA's. Read something about SELECTEDVALUE not being available in DirectQuery so I gave up.  A big THANK YOU to Samarth_18 for taking the time to try and help me out.

[Samarth_18]

Hi @antolope ,

My suggestion would be, create a column as below:-

new_column =
IF ( 'QA Review Item Pivot'[11] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[12] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[13] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[14] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[15] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[16] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[17] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[18] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[19] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[20] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[21] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[22] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[23] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[24] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[25] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[26] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[27] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[28] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[29] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[30] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[31] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[32] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[33] = "OUT", 1, 0 )
    + IF ( 'QA Review Item Pivot'[34] = "OUT", 1, 0 )

Now create a measure as below:-

Measure =
COUNTROWS ( FILTER ( table, table[new_column] > 4 ) )

Thanks,

Samarth

[antolope]

Hey Samarth_18, thank you for the suggestion. Unfortunately, I don't have access to the data model to create a calc_column and the DB admins are loaded with work. So, I thought, instead of putting in a request and waiting, that I would simply create a measure. At the same time excercising my baby DAX legs. 🙂

And if it can't be done with a measure, then I would have gained knowledge either way. Thanks again for taking the time to assist.

[Samarth_18]

Hi @antolope ,

You could create a measure as below and use it as filter on your visual:-

_filter =
VAR result =
    IF ( MAX ( 'QA Review Item Pivot'[11] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[12] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[13] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[14] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[15] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[16] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[17] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[18] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[19] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[20] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[21] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[22] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[23] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[24] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[25] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[26] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[27] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[28] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[29] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[30] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[31] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[32] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[33] ) = "OUT", 1, 0 )
        + IF ( MAX ( 'QA Review Item Pivot'[34] ) = "OUT", 1, 0 )
RETURN
    IF ( result > 4, 1, 0 )

[antolope]

Hey Samarth_18, I truly apprieciate the assist. I've tried a lot of this but glad to have the extra set of eyes.

So, when I set up the measure as suggested:

and then set up the card visual as so:

I get the below

If I switch it to MAXA, I get what I've been getting:

As mentioned in the original post, I tired setting setting up variables as suggested and I'm baffled by the error. In the attempts I've made, and your suggestion above, I'm asking PBI to look at a value, if the value matches what I'm looking for, provide a 1 (which is a numeric value), if not provide a 0 (which again is a numeric value). And in the end, add all the ones. Simple!

I'm not asking it to compare the value text "OUT" with a vlaue number 1. So why the message "do not support comparing value of type integer with values of type text"?

I'm gonna submit the request for a calc_column to the DBAs but would like to continue troubleshooting this for my own edification cause I truly don't understand what I'm doing wrong.

[Samarth_18]

@antolope can you try this please:-

_filter =
VAR result =
    IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[11] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[12] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[13] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[14] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[15] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[16] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[17] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[18] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[19] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[20] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[21] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[22] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[23] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[24] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[25] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[26] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[27] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[28] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[29] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[30] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[31] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[32] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[33] ) = "OUT", 1, 0 )
        + IF ( SELECTEDVALUE ( 'QA Review Item Pivot'[34] ) = "OUT", 1, 0 )
RETURN
    IF ( result > 4, 1, 0 )

[antolope]

Hey Samarth_18

I know for sure that SELECTEDVALUE is a function... Microsoft's documentation clearly says it is:

but for some reason PBI is telling me that it is not, SMH:

I'm on the latest version and thought that maybe SELECTEDVALUE was depricated. Not sure why my PBI is displaying this.

As a newbe to PBI, this is frustrating to say the least. Tried closing and reopening PBI, tried unsintalling and reinstalling, and still nothing. At this point it's feeling like what I am trying to do in PBI is an excersize in futility. I'm greatful for the help but I think I'm gonna let this one go and wait for the DBAs to create the calc_columns for me. Thanks for trying!