How to create a measure that shows only one of two filtered values on a card?

[Anonymous]

Hi, I have this measure here that filters on a slicer selection for Deal Names and shows the correct figure on a card. Here is the measure below:

DealPO = sumx(values('Deals (CRM)'[Deal Name]), IF(ISFILTERED('Deals (CRM)'[Deal Name]),[VO PO Total],[PO Billed]))

A PO Billed card will show one of the values that is either [VO PO Total] or [PO Billed] two measures that calculate a currency value. If a Deal has both values, then [VO PO Total] will supersede [PO Billed] and the card will show [VO PO Total] instead. The only time the card will show just the [PO Billed] value is if there is no [VO PO Total] value to the selected Deal.

So basically, I want to make it so that if [VO PO Total] has a blank value, then it will instead show the [PO Billed] value on the same card. At the moment my measure isn’t doing that, and I don’t know why.

[jdbuchanan71 · Accepted solution]

@Anonymous 

I think I understand what you are after?  Try it like this.

DealPO =
SUMX (
    VALUES ( 'Deals (CRM)'[Deal Name] ),
    IF ( ISBLANK ( [VO PO Total] ), [PO Billed], [VO PO Total] )
)

[Anonymous]

@jdbuchanan71 Thank you very much! That worked a treat

[jdbuchanan71]

@Anonymous 

If your data set is big enough then calculating the [VO PO Total] twice could get slow.  To avoid that we can put it in a variable inside the SUMX iterator instead.

DealPO =
SUMX (
    VALUES ( 'Deals (CRM)'[Deal Name] ),
    VAR _POTotal = [VO PO Total]
    VAR _POBilled = [PO Billed]
    RETURN
        IF ( ISBLANK ( _POTotal ), _POBilled, _POTotal )
)

[Anonymous]

@jdbuchanan71 Thank you, I've just used this. I do have a lot of data, currently 9 tables all linked together with thousands of rows so this helps a lot. Really appreciate it! 🙂