topic Validating date row, cannot determine what to compare for missing date row - using IF in DAX Commands and Tips

Validating date row, cannot determine what to compare for missing date row - using IF

rocky_puff — Thu, 25 Mar 2021 00:52:23 GMT

Hi. Currently I have a task where I need to validate - if a Shop declared its status that day, so a Date row will created in the dataset. When I want to make a summary whether the Shop had declared or not on that month, I will validate the date row and if it exist, a table of declaration will be produced - containing a 'Yes' (declared) or 'No' (does not declare). I validated the Date row with a Master Date table.

But the issue here now is, when a Shop did not declare, and when I validate, my code(or power bi, not sure what's wrong) DOES NOT declare the missing Date as 'No'. I know whats wrong here, but I cannot figure out the solution. Due to non existent row in the dataset, power bi does not have anything to compare and validate with, thus leaving it blank instead of 'No'. Adding manually the date on the dataset, is not a very good idea for future sustainability.

Does anyone has any idea how to solve this issue efficiently?

Need your help, Thank you!

For example here, Shop F04 did not declare their status on 27/2/2021 and 28/2/2021. There is no "No" value in the table. Here I attached together my proposed formula.

Re: Validating date row, cannot determine what to compare for missing date row - using IF

amitchandak — Thu, 25 Mar 2021 05:01:48 GMT

rocky_puff , Not clear on the need of this column.

you can create a measure

measure = if(countrows(Table)+0=0,"No", "Yes")

Jan , feb should be filter from UI on the visual

Re: Validating date row, cannot determine what to compare for missing date row - using IF

rocky_puff — Thu, 25 Mar 2021 06:53:39 GMT

I've tried the measure you proposed. But still can't get the "No".

The Comparison Column is to show the result after validating whether the date exist or not (in other word, to validate whether the shop did or did not declare their status on that day)

Re: Validating date row, cannot determine what to compare for missing date row - using IF

rocky_puff — Thu, 01 Apr 2021 01:34:32 GMT

I have found the solution. Maybe I overthink too much. So here is the solution that I used for my problem (eventhough im not sure how it works, but it works!) 🙂

Declaration = IF(MAX(February2021[Date]) == BLANK(), "No", "Yes")

It will then produce the outcome that I want ! Thanks everyone, I hope it helps you too 🙂