How to get a deafult string value for blanks in a Power BI Desktop measure

Question

I have two tables ( Sales[OrderNumer,Price,ItemId,...] and Item[Id, Name, Type] ) and I have a measure that returns a string like that:

 ItemsType1 = CALCULATE(
     CONCATENATEX('Sales', RELATED('Item'[Name]), ","), 'Item'[Type] = "type1"

I have a table visual in Power BI Desktop at Order level with the measure ItemsType1 [OrderNumer, ItemsType1]. I want to change BLANK values of ItemType1 column by a default value like "n/a" and I rewrote the measure as:

 ItemsType1 = VAR x = CALCULATE(
     CONCATENATEX('Sales', RELATED('Item'[Name]), ","), 'Item'[Type] = "type1") 
 RETURN IF( ISBLANK(x), "n/a", x )

The measure returns the expected value, but in this way, for my 600000 rows in Sales table, the memory grows abruptly and the visual collapses. I have 16GB of memory. I think the problem is with the IF statement but I do not know why. I would like to understand why this difference in performance and what is the best way to do this.

Example

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-30T15:33:09

I'm pretty sure the reason it's blowing up is that because of the way you have it written, it will return "n/a" for ALL possible things in the table, not just the type1 ones you're filtering on.

The solution is to move the IF inside the concatenation. Try something like this:

ItemsType1 =
CALCULATE (
    CONCATENATEX ( 'Sales',
        IF(ISBLANK( RELATED ( 'Item'[Name] ) )
            "n/a",
            RELATED ( 'Item'[Name] )
        ),
    "," ),
    'Item'[Type] = "type1"
)

The above isn't working quite how I expected. Try changing your RETURN line to this instead:

RETURN
    IF(
        COUNTROWS( RELATEDTABLE( 'Item' ) ) = 0,
        BLANK(),
        IF( ISBLANK( x ), "n/a", x )
    )

This checks for any empty related table before doing any heavier computing.

How to get a deafult string value for blanks in a Power BI Desktop measure

1 Answers