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
Avivek
Post Partisan
Post Partisan

Help with DAX

I have a report the requirement for which is i need to show the all the orders which has been entered without the id. in a visual i can see this by adding it in the filter. 

But now  I have to show a table and a chart with 2 different columns, one column shows all the order's with id and another column shows all the order's without the id. I tried to create a DAX,

Column = CALCULATE(DISTINCTCOUNT('SURGEON SALES'[ORDER_ID]),FILTER('SURGEON SALES','SURGEON SALES'[NPI]="BLANK()"))
Below image is for ref.

Avivek_1-1600277203784.png

The 2nd row which is  count of order id is achieved by adding a filter but when I try to add it through DAX, my dax gives no value.

Can anyone would help me to understand why my DAX is not working. And is there any other solution for this where in 1 column  i can see order's with the id and another column without the id's.

 

1 ACCEPTED SOLUTION
richbenmintz
Solution Sage
Solution Sage

Hi @Avivek,

 

Not sure why you have Blank() in Quotations, it is not a string but the equivalent of NULL, following should fix that issue.

Column = CALCULATE(DISTINCTCOUNT('SURGEON SALES'[ORDER_ID]),FILTER('SURGEON SALES','SURGEON SALES'[NPI]=BLANK()))

 However given that it looks like you are creating a column to count the following would be easier

Column = IF('SURGEON SALES'[NPI]=BLANK(), 1, blank())

Better yet create a measure that counts the rows you are interested in

 

 Blank ID = CALCLUATE(COUNTROWS('SURGEON SALES'), 'SURGEON SALES'[NPI]=BLANK())

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

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

Proud to be a Super User!


View solution in original post

4 REPLIES 4
richbenmintz
Solution Sage
Solution Sage

Hi @Avivek,

 

Not sure why you have Blank() in Quotations, it is not a string but the equivalent of NULL, following should fix that issue.

Column = CALCULATE(DISTINCTCOUNT('SURGEON SALES'[ORDER_ID]),FILTER('SURGEON SALES','SURGEON SALES'[NPI]=BLANK()))

 However given that it looks like you are creating a column to count the following would be easier

Column = IF('SURGEON SALES'[NPI]=BLANK(), 1, blank())

Better yet create a measure that counts the rows you are interested in

 

 Blank ID = CALCLUATE(COUNTROWS('SURGEON SALES'), 'SURGEON SALES'[NPI]=BLANK())

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz , thanks for the rep;y and also it works. but can you please help me to understand how does the dax i shared earlier does not work in a calculated column but works fine in a measure

Hi @Avivek,

 

Let me try,

 

Your original formiula had Blank() in quotes, like "Blank()" so your formula was looking for the string "Blank()" not the value Blank() aka null. 

Given that you are creating a calculated column and it will be evaluated in the context of the row there is no reason to perform a distinct count of the id, you simply need to check for the existince of the id and return a 1 or null, then count the results in a measure.

 

Hope that makes sense.



I hope this helps,
Richard

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

Proud to be a Super User!


thankyou. I got it now🙂

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.