Forum Discussion
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 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
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.
7 Replies
Samarth_18Community Champion
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
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 )
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.