topic Re: I think I need a count rows or group by but it doesn't work in DAX Commands and Tips

I think I need a count rows or group by but it doesn't work

Reetz — Wed, 06 May 2020 15:27:10 GMT

I have a survey table that I am trying to find the unique # of rooms where the Meet Criteria equals YES. I don't have access to the table so can't transform it. I think I need to either group by or count rows, but can't figure out how. Can someone help please? Thanks in advance!!!

Re: I think I need a count rows or group by but it doesn't work

Greg_Deckler — Wed, 06 May 2020 15:37:18 GMT

COUNTROWS(
SUMMARIZE(
FILTER('Table',[Meet Criteria] = "Yes"),
[Room]
)
)

Re: I think I need a count rows or group by but it doesn't work

Reetz — Wed, 06 May 2020 16:08:12 GMT

Greg_Deckler Thank you so much for your quick response. The Room number/Match in the middle of my picture was just an illustration to show the data easily. My actual table that I am trying to write my dax off of is on the left.

Re: I think I need a count rows or group by but it doesn't work

Greg_Deckler — Wed, 06 May 2020 16:28:59 GMT

Oh, you need to first add a column using EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

I probably create an entirely new table maybe, like:

Table = ADDCOLUMNS( FILTER('Table',[Question] = "Room #") "Meets Criteria",MAXX(FILTER('Table',[Survey]=EARLIER([Survey] && [Survey Response] = EARLIER([Survey Response]),[Response]) )

Re: I think I need a count rows or group by but it doesn't work

Reetz — Wed, 06 May 2020 18:45:12 GMT

Greg_Deckler Thank you for this explaination!! I am newer to power bi and am not familiar with some of these functions. I did read your article, and tried the DAX you provided but am getting the attached error. Have tried looking up what the error may be but am still getting this error.

Re: I think I need a count rows or group by but it doesn't work

Greg_Deckler — Wed, 06 May 2020 21:15:44 GMT

Sorry, I totally screwed up the parens on that:

Table = ADDCOLUMNS( FILTER('Table',[Question] = "Room #") "Meets Criteria", MAXX( FILTER( 'Table', [Survey]=EARLIER([Survey]) && [Survey Response] = EARLIER([Survey Response]) ), [Response]) ) )

Re: I think I need a count rows or group by but it doesn't work

Reetz — Thu, 07 May 2020 14:12:52 GMT

Thank you. This worked!!!!! 🙂