cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narender
Resolver I
Resolver I

Add a null condition in DAX

Hi all,

 

I have to add a null condition in DAX.

 

My condition is like:

 

Measure =

CALCULATE(sum(TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),

FILTER(TAX_TRANSACTION,TAX_TRANSACTION[TAX_SUB_TRANS.REVERSE_FLAG]="N" && (TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2 ||TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=13 ||TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=15))) 

 

I need to add TAX_SUB_TRANS.REVERSE_FLAG]=null or "N"

 

In above expression I write only TAX_SUB_TRANS.REVERSE_FLAG] "N"  because Null is not working.

 

Plaese tell mw how  can I add Reverse_flag = null?

 

 

Thanks,

 

Narender

 

 

 

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

Null is BLANK() in DAX
https://msdn.microsoft.com/en-us/query-bi/dax/blank-function-dax
try this syntax

TAX_TRANSACTION[TAX_SUB_TRANS.REVERSE_FLAG] = BLANK()

 

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Stachu
Community Champion
Community Champion

Hi @zlokesh
I think you're incorrect - ISBLANK is checking whther a given expression is BLANK or not, returning TRUE/FALSE

https://msdn.microsoft.com/en-us/query-bi/dax/isblank-function-dax

BLANK() will return a blank/null value
https://msdn.microsoft.com/en-us/query-bi/dax/blank-function-dax



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

7 REPLIES 7
zlokesh
Resolver I
Resolver I

Hi @Narender,

 

      There is  ISBLANK function in Power BI, use that, If any issue let me know.

 

msdn Example:- //Sales to Previous Year Ratio

 

=IF( ISBLANK('CalculatedMeasures'[PreviousYearTotalSales]) , BLANK() , ( 'CalculatedMeasures'[Total Sales]-'CalculatedMeasures'[PreviousYearTotalSales] ) /'CalculatedMeasures'[PreviousYearTotalSales])

 

 

Thanks

Hello Lokesh,

 

I  have to add simple condition not the if condition like

Sum of sale where regime_code = N or Null

 

Is there any way to add this condition in DAX.

I am facing problem in Null .If condition will not resovle my issue.

 

 

Thanks,

 

Narender

 

 

Dear @Narender,

 

  ISBLANK  is the function for NULL.   use ISBLANK  to check NULL.

 

Your expression will be like below, Please check in editor for accuracy. 

 

Measure =

CALCULATE(ISBLANK(sum(TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),

FILTER(TAX_TRANSACTION,TAX_TRANSACTION[TAX_SUB_TRANS.REVERSE_FLAG]="N" )&& (TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2 ||TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=13 ||TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=15))) 

 

 

 

Thanks

Stachu
Community Champion
Community Champion

Hi @zlokesh
I think you're incorrect - ISBLANK is checking whther a given expression is BLANK or not, returning TRUE/FALSE

https://msdn.microsoft.com/en-us/query-bi/dax/isblank-function-dax

BLANK() will return a blank/null value
https://msdn.microsoft.com/en-us/query-bi/dax/blank-function-dax



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

It's not quite correct though because if they use the blank in a calculation it will count as 0 not NULL. Which will introduce errors. 

 

As in this article https://www.sqlbi.com/articles/how-to-handle-blank-in-dax-measures/ 

Hi Stachu,

 

Blank() is working in Dax.

Like

Reverse_Flag= N || Reverse_Flag=Blank().

 

 

 

 

Stachu
Community Champion
Community Champion

Null is BLANK() in DAX
https://msdn.microsoft.com/en-us/query-bi/dax/blank-function-dax
try this syntax

TAX_TRANSACTION[TAX_SUB_TRANS.REVERSE_FLAG] = BLANK()

 

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors