table shows all rows when adding ISBLANK to the measure

[Richard_Halsall]

Hi

I have a simple data model with fact and dim tables as below

I create a table visual and the results are as expected

External Cost is this measure

PO Cost External =

var external = CALCULATE(

    SUM(FactPOL[Total_Cost__c]),

    DimPO[Supplier] <> "Power US"

)

RETURN

external

Now when I change the measure to

PO Cost External =

var external = CALCULATE(

    SUM(FactPOL[Total_Cost__c]),

    DimPO[Supplier] <> "Power US"

)

RETURN

IF(ISBLANK(external), 0, external)

All the technician names are returned in the table

I am unable to put a filter on the visual PO Cost External <> 0 as there will be times when the  SUM(FactPOL[Total_Cost__c]) will actually be zero

Other than removing the IF(ISBLANK can this be resolved another way?

Thanks in advance

[amitchandak · Accepted solution]

@Richard_Halsall , The first one should work when sum is 0. Can you share output you want.

Try measures likes

M1= CALCULATE(
SUM(FactPOL[Total_Cost__c]),
DimPO[Supplier] <> "Power US"
)

PO Cost =
Switch(ISBLANK([M1]), blank() ,
[M1]=0, blank(), [M1] )

Or

PO Cost =
Switch(ISBLANK([M1]), blank() ,
[M1]=0, 0, [M1] )