topic Re: Multiple COUNTROWS? in DAX Commands and Tips

Multiple COUNTROWS?

smoothbrain — Thu, 27 Jun 2024 01:07:47 GMT

Hey team,

I'm a chimp at a typewriter trying to lean into PowerBI. Not terrifically experience with DAX, but am the desinated 'data' person.

Is it possible to sum multiple Countrows? An active participant in a program is assined a value of 5, whereas a passive participant is assigned a value of 2?

CALCULATE (COUNTROWS('DataTable'), FILTER('DataTable','DataTable'[Status]="Active" &&'DataTable'[Program]="Program1"))*5

Then I'm assuming the second half would be something to the effect of:

CALCULATE (COUNTROWS('DataTable'), FILTER('DataTable','DataTable'[Status]="Passive" &&'DataTable'[Program]="Program1"))*2

Am I able to get a sum of the total? The value assigned depends on the program, and it spans across a couple of programs.

If anyone could steer in the right direciton, I'd be very appreciative!

Back to Googling for now!

Re: Multiple COUNTROWS?

aduguid — Thu, 27 Jun 2024 01:14:44 GMT

I would create a calculated column in your table. Then all you need to do is SUM the calculated column.

NewStatusColumn = SWITCH( TRUE(), 'DataTable'[Program] = "Program1" && 'DataTable'[Status] = "Active", 5, 'DataTable'[Program] = "Program1" && 'DataTable'[Status] = "Passive", 2, BLANK() // Default value if none of the conditions are met )

Re: Multiple COUNTROWS?

tharunkumarRTK — Thu, 27 Jun 2024 02:18:45 GMT

smoothbrain

You can do it in this way.

SUMX(DataTable, if( 'DataTable'[Status] = "Passive" && 'DataTable'[Program] = "Program1", 2, if( 'DataTable'[Status] = "Active" && 'DataTable'[Program] = "Program1", 5)))

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun