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
Icon for Community Champion rankCommunity Champion
4 years ago

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

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's avatar
    Samarth_18
    Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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 )