Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amitkr174
Helper III
Helper III

Need help on DAX

DAX.PNGHi,

 

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"))
 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

 

View solution in original post

MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.