Multiple COUNTROWS?

[smoothbrain]

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! 

[aduguid · Accepted solution]

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
)

[tharunkumarRTK · Accepted solution]

@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

[aduguid · Accepted solution]

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
)