IFERROR then NA in Power BI

0
votes

I have two tables are data and report.

The data table contains two columns: item and text. The item and text columns contain duplicates.

I am applying the following calculated column in the report table

RESULT =
CALCULATE (
    FIRSTNONBLANK ( DATA[TEXT], TRUE () ),
    FILTER ( DATA, DATA[ITEM] = REPORT[ITEM] )
)

In order to get the data from the data table into the report table.

  1. If the item is not available in the data table, then return “NA” according to the item in the report table. My calculated column is showing blanks if the item is not available in the data table but I want "NA" instead of blanks.

  2. My calculated column is picking the min qty according to the item in the report table based on the data table where the same item contains multiple qty but I want max qty in the report table according to the item where the same item contains multiple qty.

Can you please advise?

Herewith attached the power bi for your reference.

https://www.dropbox.com/s/e3fa45f8qz8a2tm/LOOKUP%20MULTIPLE%20VALUE_QTY.pbix?dl=0

Data & Report:

enter image description here

enter image description here

1
powerbidaxpowerquery

1 Answers

1
votes

You can get the max with just

RESULT = CALCULATE ( MAX ( DATA[TEXT] ) )

Replacing blanks with NA is possible but it means the RESULT column must be converted to a text data type (since a column cannot have multiple data types).

I don't advise doing that, but you can like this:

RESULT = 
VAR MaxText = CALCULATE ( MAX ( DATA[TEXT] ) )
RETURN
    IF ( ISBLANK ( MaxText ), "NA", FORMAT ( MaxText, "0" ) )

Result