Need help on DAX

[Amitkr174]

Hi,

I need some help with DAX. Below is the DAX code which I am using to derive the backlog type.

This DAX is working fine but the issue is that at the total level I am seeing 'Bill in advance' (highlighted in yellow) which I want to remove. Please suggest something on this.

Backlog Type =

IF(LASTNONBLANK('ADLS Backlog'[Product],1)=BLANK(),BLANK(),

IF(SEARCH("TBE",LASTNONBLANK('ADLS Backlog'[Product],1),1,0),"Bill in Arrears", "Bill in Advance"))

[Anonymous · Accepted solution]

You could add something like "IF( HASONEVALUE( Table[Deal Registration ID] ), [[your measure]], BLANK() )". Since you have more than one value for this column on the total level, but a single value for each row of your visual, this should return the same values in each row and no value on the total level.

Regards

[MFelix · Accepted solution]

Hi @Amitkr174 ,

Measure are based on context so when you add a measure to a table/matrix the value is still calculated on the last row, depending on the values you have for aggregations the result can be different.

You can change your measure to:

Backlog Type =
IF (
    HASONEVALUE ( Table[Geo] ),
    IF (
        LASTNONBLANK ( 'ADLS Backlog'[Product], 1 ) = BLANK (),
        BLANK (),
        IF (
            SEARCH ( "TBE", LASTNONBLANK ( 'ADLS Backlog'[Product], 1 ), 1, 0 ),
            "Bill in Arrears",
            "Bill in Advance"
        )
    ),
    BLANK ()
)

You need to replace the Table[Geo] by the name of the table where the column Geo is located, you can also use any other column on your table, the idea is that if theres is a single value for geo it returns your meausre other wise is blank.

[Amitkr174]

Thank you so much for the help!

Below DAX is working fine :-

ADLS Backlog Type =

IF(HASONEVALUE('ADLS Backlog'[Product]),

      IF(SEARCH("TBE",LASTNONBLANK('ADLS Backlog'[Product],1),1,0),"Bill in Arrears", "Bill in Advance"),

        BLANK()

   )

[Anonymous · Accepted solution]

You could add something like "IF( HASONEVALUE( Table[Deal Registration ID] ), [[your measure]], BLANK() )". Since you have more than one value for this column on the total level, but a single value for each row of your visual, this should return the same values in each row and no value on the total level.

Regards