Forum Discussion
UNION() on date field containing blanks
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.
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
9 Replies
IrwanSuper User
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.
- 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
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
