UNION() on date field containing blanks

[ChrisD_UK]

I have a DAX Query in my .pbix. In the script, a UNION() either takes ages or doesn't finish at all and has to be cancelled. This only happens when the tables in question contain a date field and some of the dates are blank. The date field isn't the date on a Date Table, it's just a plain simple data field. The problem goes away if the same date field has been transformed to be a text field. Why does this happen and how can it be resolved? The attached .pbix illustrates the problem as simply as possible. The scenario is currently set up to fail. The commented-out line of M code prevents the issue but of course leaves a field that should be a date as text.  Thanks.

UNION() on date field containing blanks.pbix 

Sale.xlsx 

[maruthisp]

Hi ChrisD_UK,

As per you original post, can you try below steps:
1. Can you replace Blank Dates with a placeholder date, something like below with DAX:
CleanedDate =
IF(
ISBLANK('Table'[YourDateColumn]),
DATE(1900, 1, 1), 
'Table'[YourDateColumn]
)

2. Or, filter out blank dates before UNION like below, if blanks are not needed in the result:
UNION(
FILTER(Table1, NOT ISBLANK(Table1[Date])),
FILTER(Table2, NOT ISBLANK(Table2[Date]))
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ChrisD_UK]

Hi @maruthisp .

Thanks for the response.  Option 1 helped greatly but Option 2 didn't make any difference.  After doing the UNION() based on Option 1, I also needed to convert the 1/1/1900 dates back to BLANK, i.e. IF([Date] = DATE(1900, 1, 1), BLANK(), [Date]).

Happy to approve this as solution to my problem if you can incorporate the above.  Also, it might be worth highlighting that this is a slightly cumbersome workaround, albeit a neccessary one.  Feel free to correct me, but the underlying reason why the issue happens is still unexplained.

[maruthisp]

Hi @ChrisD_UK ,

I was able to download your pbix file and excel file. Now, I have the setup to work on.
But, I am missing what are you looking for as a end result.
Please let me know what are you trying to achieve? Provide some image with output or some sample output.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ChrisD_UK]

Maruthi,

You can see the results I'm trying to achieve by running the DAX Query in this revised version of my .pbix.  UNION() on date field containing blanks (with workaround).pbix  As the file name suggests, this is where I proved your suggestion.  You'll see your name against the code I've added.  Thanks.

Chris.

[v-mdharahman]

Hi @ChrisD_UK,

Thanks for reaching out to the Microsoft fabric community forum.

Just following up to your previous messages. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

I would also take a moment to thank @maruthisp and @Irwan, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[maruthisp]

Hey ChrisD_UK,
Sorry for late response. I downloaded the pbix file and I couldn't find any DAX query inside the pbix file. Or am I missing something?
Please let me know and thanks in advance.

Best Regards,

Maruthi

[Irwan]

hello @ChrisD_UK 

i might be misunderstood because there is only sample data in your pbix (not sure the problem is), but there is some catches for UNION().

as far as i know, you need to have same header column name and same size for union to be worked.

also for filtering blank, you can add FILTER inside UNION. something like below.

UNION(
   FILTER('tabl1',not ISBLANK('date'),
   FILTER('tabl2',not ISBLANK('date')
)

Hope this will help.

Thank you.