Forum Discussion

antolope's avatar
antolope
Regular Visitor
4 years ago
Solved

Count Rows with XX number of Columns Out

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...
General Comment
Need Help
Tips and Tricks
  • antolope's avatar
    antolope
    4 years ago

    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's avatar
Samarth_18
Community Champion
4 years ago

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's avatar
antolope
Regular Visitor
4 years ago

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's avatar
    Samarth_18
    Community Champion
    4 years ago

    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's avatar
      antolope
      Regular Visitor
      4 years ago

      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!