topic Re: Handling blank values and ignoring additional fields DAX in DAX Commands and Tips

Handling blank values and ignoring additional fields DAX

DivakarKrishnan — Sun, 13 Sep 2020 10:24:10 GMT

Dear Team,

I am using below DAX values in my below matrix visual. However, I am seeing many blank values.

DAX 1:

Issue_SR_Open_Ageing = CALCULATE(COUNT(Issues[Created week] ),FILTER('Issues','Issues'[ISSUE_CURRENT_TYPE_NAME] in {"Service Request","Task","Service Request with Approvals"}),

FILTER('Issues', 'Issues'[ISSUE_RESOLUTION]=""),FILTER(

'Issues', LEFT('Issues'[ISSUE_KEY],3) ="GTS"))

To replace the blank values, I am using ISBLANK() and changing it to ‘0’. But I am facing an issue, it’s not showing ‘0’ for all columns as per below visual and it’s adding few more columns(Blocker, Critical, High) which has already count is zero and I don’t need these fields due to count is already zero.

DAX 2:

Validating Counts = IF (ISBLANK([Issue_SR_Open_Ageing]),0,[Issue_SR_Open_Ageing])

Kindly help on the same to replace blank with '0' and maintain the same fields as pic1.

Re: Handling blank values and ignoring additional fields DAX

Fowmy — Sun, 13 Sep 2020 13:10:26 GMT

DivakarKrishnan

You cannot fill all the cells with blank to ZERO when you do not have data for the intersection of ROW and Column. Your measure DAX2 will not work in that case.

Blocker, Critical, High are showing up as the measure is able to produce a blank value.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Handling blank values and ignoring additional fields DAX

DivakarKrishnan — Sun, 13 Sep 2020 13:13:45 GMT

ok, but any other work around or suggestion to handle this scenario please. Requirement is they don't want to see blank values.

Re: Handling blank values and ignoring additional fields DAX

Anonymous — Sun, 13 Sep 2020 16:20:10 GMT

DivakarKrishnan

Issue_SR_Open_Ageing = CALCULATE(COUNT(Issues[Created week] ),FILTER('Issues','Issues'[ISSUE_CURRENT_TYPE_NAME] in {"Service Request","Task","Service Request with Approvals"}),

FILTER('Issues', 'Issues'[ISSUE_RESOLUTION]=""),FILTER(

'Issues', LEFT('Issues'[ISSUE_KEY],3) ="GTS")) + 0

Try adding 0