Two count calculation showing together in one measure

[Anonymous]

With above data am trying to create a column  that contains two count calculation,

Count 1 =
CALCULATE(
COUNTROWS( myTable ),
myTable[endt] = 0,
FILTER( myTable, myTable[type] = "Cancel" || myTable[type] = "endt" )
)

Count 2 =
CALCULATE(
COUNTROWS( myTable ),
myTable[endt] > 0,
FILTER( myTable, myTable[type] = "new" || myTable[type] = "renewal" )
)

The condition was this...

1)Count for type where endt=0 for types(Cancel,endt)

2)Count for type where endt greater than 0 for types (new,renewal).

if 1 condition satisfy means count calculation based on that if not 2nd calcualtion wants to do...So do both calculation do based on conditions in one column output would be shown 

Is it possible? 

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

Measure = 
    var Count1 = 
        CALCULATE(
            COUNTROWS( 'Table' ),
            'Table'[endt] = 0,
            FILTER( 
                'Table', 
                'Table'[type] = "Cancel" || 'Table'[type] = "endt" 
            )
        )
    var Count2 = 
        CALCULATE(
            COUNTROWS( 'Table' ),
            'Table'[endt] > 0,
            FILTER( 
                'Table', 
                'Table'[type] = "new" || 'Table'[type] = "renewal" 
            )
        )
    var result = 
        IF(
            MAX('Table'[type]) = "Cancel" || MAX('Table'[type]) = "endt" ,
            "A",
            "B"
        )
    return 
        IF(
            result = "A",
            Count1,
            Count2
        )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

I don't fully understand your explanation but you could try this measure

Count = 

VAR _Count1 = CALCULATE(COUNTROWS( myTable ), FILTER( myTable, (myTable[type] = "Cancel" || myTable[type] = "endt") && myTable[endt] = 0 ))

VAR _Count2 = CALCULATE(COUNTROWS( myTable ), FILTER( myTable, (myTable[type] = "new" || myTable[type] = "renewal") && myTable[endt] > 0 ))

RETURN

IF(SELECTEDVALUE(myTable[endt]) = 0, _Count1, _Count2)

Regards

Phil

[amitchandak]

@Anonymous , No very clear to me.

You can have a third calcultion like

if(isblank([Count 1]), [Count 2], [Count 1])