cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

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

Accepted Solutions
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!

View solution in original post

4 REPLIES 4
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!

View solution in original post

@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.

thankyou. I got it now🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors