Calculated metric value resulting in cartesian results between dimensions

[nbitabc]

I am trying to add a custom flag value through a measure but it is returning multiple rows with blank metric value.

Here is a simple scenario - 

We have two rows for this Business Class, Customer Segment combination in our database for metric Total Amount Paid

Now we try to add a Flag value of Calculated Measure 

Service_Flag = IF (MAX(BUSINESS_CLASS[Business Class]) = "Auto Service Station" && MAX(CUSTOMER_SEGMENT[Customer Segment Code]) = "MOTORCYCLE_POWERSPORT_DEALER" && [Total Amount Paid] > 10000,1,0)

We get the right result but the report seems to bring in all combination of Dimensions Business Class and Customer Segment with blank Total Amount values. 

How do we get just the rows relevant with Metric values and suppress the blank metric rows?

[Ashish_Mathur]

Hi,

You can expand the filter pane and add a condition of 1 for that measure.

[nbitabc · Accepted solution]

Thank you @OwenAuger. This worked prefectly !

[OwenAuger]

Hi @nbitabc 

A quick fix is to add a condition that [Total Amount Paid] is nonblank, and also use a variable to avoid repeated evaluation:

Service_Flag =
VAR TotalAmountPaid = [Total Amount Paid]
RETURN
    IF (
        NOT ISBLANK ( TotalAmountPaid ),
        IF (
            MAX ( BUSINESS_CLASS[Business Class] ) = "Auto Service Station"
                && MAX ( CUSTOMER_SEGMENT[Customer Segment Code] ) = "MOTORCYCLE_POWERSPORT_DEALER"
                && TotalAmountPaid > 10000,
            1,
            0
        )
    )

I suggest putting this condition in an outer IF to avoid evaluating the existing expression unless necessary.

Does this work for you?