Forum Discussion
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,
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.
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!
4 Replies
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!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.
