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

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!