cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Solution Sage
Solution Sage

Re: Help with DAX

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
Highlighted
Solution Sage
Solution Sage

Re: Help with DAX

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

Highlighted
Helper V
Helper V

Re: Help with DAX

@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

Highlighted
Solution Sage
Solution Sage

Re: Help with DAX

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.

Highlighted
Helper V
Helper V

Re: Help with DAX

thankyou. I got it now🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors