topic Re: Date parameter report builder format in DAX Commands and Tips

Date parameter report builder format

Foguete — Fri, 20 Jan 2023 15:13:02 GMT

Hello,

I am currently using a query with a Power BI dataset with a date parameter.

When I configure the parameter as Text I don't have any problem but when I configure it as a Date and Time, I got the following error:

Query (1, 106) DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I believe I should modify my DAX query but I have no idea how. Could someone explain to me the way to handle this type of issue ?

My query so far:

DEFINE VAR vFromInformeStockDelegaciF3nJDFEC1 = IF(PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) = 1, IF(@FromInformeStockDelegaciF3nJDFEC <> "", @FromInformeStockDelegaciF3nJDFEC, BLANK()), IF(PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2) <> "", PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2), BLANK()))
VAR vFromInformeStockDelegaciF3nJDFEC1ALL = PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) > 1 && PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Informe Stock Delegación'[JDFEC], 'Informe Stock Delegación'[Compañia], 'Informe Stock Delegación'[TOTAL PALLETS_STOCK], RSCustomDaxFilter(@InformeStockDelegaciF3nCompaF1ia,EqualToCondition,[Informe Stock Delegación].[Compañia],String), FILTER(VALUES('Informe Stock Delegación'[JDFEC]), (vFromInformeStockDelegaciF3nJDFEC1ALL || 'Informe Stock Delegación'[JDFEC] >= DATEVALUE(vFromInformeStockDelegaciF3nJDFEC1) + TIMEVALUE(vFromInformeStockDelegaciF3nJDFEC1))))

Thanks

Re: Date parameter report builder format

Anonymous — Tue, 24 Jan 2023 05:55:49 GMT

Hi Foguete ,

This is because in your dax formula that comparing values of type Date with values of type Text, you need add a function to transform data type.

Please try to use Format() function to convert data type.

For more details, you can read related document: FORMAT - DAX Guide

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Date parameter report builder format

Foguete — Tue, 24 Jan 2023 08:26:42 GMT

Hello,

Thanks for the answer but I am not really sure about how to apply the modification.

So far I have been modifying the query for something like this using FORMAT() (modification in red):

DEFINE VAR

vFromInformeStockDelegaciF3nJDFEC1 =

IF(

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) = 1,

IF(@FromInformeStockDelegaciF3nJDFEC <> "", @FromInformeStockDelegaciF3nJDFEC, BLANK()),

IF(PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2) <> "", PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2), BLANK())

)

VAR

vFromInformeStockDelegaciF3nJDFEC1ALL =

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) > 1 && PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 1, 1) < 1

EVALUATE

SUMMARIZECOLUMNS(

FORMAT('Informe Stock Delegación'[JDFEC], "dd/mm/yyyy"), 'Informe Stock Delegación'[Compañia], 'Informe Stock Delegación'[TOTAL PALLETS_STOCK],

RSCustomDaxFilter(@InformeStockDelegaciF3nCompaF1ia,EqualToCondition,[Informe Stock Delegación].[Compañia],String),

FILTER(VALUES('Informe Stock Delegación'[JDFEC]), (vFromInformeStockDelegaciF3nJDFEC1ALL || 'Informe Stock Delegación'[JDFEC] >= DATEVALUE(vFromInformeStockDelegaciF3nJDFEC1) + TIMEVALUE(vFromInformeStockDelegaciF3nJDFEC1))))

It is giving me error, and I am not sure if it is my syntax or the position of my modification which is throwing error...

Re: Date parameter report builder format

Anonymous — Tue, 24 Jan 2023 08:44:10 GMT

Hi Foguete ,

You modify position is not correct, please try below modification (see red color part):

DEFINE VAR

vFromInformeStockDelegaciF3nJDFEC1 =

IF(

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) = 1,

IF(@FromInformeStockDelegaciF3nJDFEC <> "", @FromInformeStockDelegaciF3nJDFEC, BLANK()),

IF(PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2) <> "", PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2), BLANK())

)

VAR

vFromInformeStockDelegaciF3nJDFEC1ALL =

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) > 1 && PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 1, 1) < 1

EVALUATE

SUMMARIZECOLUMNS(

'Informe Stock Delegación'[JDFEC], 'Informe Stock Delegación'[Compañia], 'Informe Stock Delegación'[TOTAL PALLETS_STOCK],

RSCustomDaxFilter(@InformeStockDelegaciF3nCompaF1ia,EqualToCondition,[Informe Stock Delegación].[Compañia],String),

FILTER(VALUES('Informe Stock Delegación'[JDFEC]), (vFromInformeStockDelegaciF3nJDFEC1ALL || FORMAT('Informe Stock Delegación'[JDFEC], "dd/mm/yyyy") >= DATEVALUE(vFromInformeStockDelegaciF3nJDFEC1) + TIMEVALUE(vFromInformeStockDelegaciF3nJDFEC1))))

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Date parameter report builder format

Foguete — Tue, 24 Jan 2023 08:57:40 GMT

Anonymous

Thanks, but your code is throwing the following error:

Query (5, 16) DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Do you have any idea where could it come from ?

Re: Date parameter report builder format

Anonymous — Wed, 25 Jan 2023 08:45:15 GMT

Hi Foguete ,

Please try below:

DEFINE VAR

vFromInformeStockDelegaciF3nJDFEC1 =

IF(

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) = 1,

IF(@FromInformeStockDelegaciF3nJDFEC <> "", @FromInformeStockDelegaciF3nJDFEC, BLANK()),

IF(PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2) <> "", PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2), BLANK())

)

VAR

vFromInformeStockDelegaciF3nJDFEC1ALL =

PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) > 1 && PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 1, 1) < 1

EVALUATE

SUMMARIZECOLUMNS(

'Informe Stock Delegación'[JDFEC], 'Informe Stock Delegación'[Compañia], 'Informe Stock Delegación'[TOTAL PALLETS_STOCK],

RSCustomDaxFilter(@InformeStockDelegaciF3nCompaF1ia,EqualToCondition,[Informe Stock Delegación].[Compañia],String),

FILTER(VALUES('Informe Stock Delegación'[JDFEC]), (vFromInformeStockDelegaciF3nJDFEC1ALL || DATEVALUE('Informe Stock Delegación'[JDFEC]) >= DATEVALUE(vFromInformeStockDelegaciF3nJDFEC1) + TIMEVALUE(vFromInformeStockDelegaciF3nJDFEC1))))

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Date parameter report builder format

Foguete — Wed, 25 Jan 2023 16:20:51 GMT

Hi Anonymous,
Unfortunately no, still the same message. I am also looking into it but cannot find the solution either

Re: Date parameter report builder format

Foguete — Thu, 23 Mar 2023 09:22:36 GMT

Just in case it might be usefull for someone, here is what makes it work:

DEFINE VAR vFromInformeStockDelegaciF3nJDFEC1 = IF(PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) = 1, IF(@FromInformeStockDelegaciF3nJDFEC <> "", @FromInformeStockDelegaciF3nJDFEC, BLANK()), IF(PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2) <> "", PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 2), BLANK()))
VAR vFromInformeStockDelegaciF3nJDFEC1ALL = PATHLENGTH(@FromInformeStockDelegaciF3nJDFEC) > 1 && PATHITEM(@FromInformeStockDelegaciF3nJDFEC, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Informe Stock Delegación'[JDFEC], 'Informe Stock Delegación'[Compañia], 'Informe Stock Delegación'[TOTAL PALLETS_STOCK], RSCustomDaxFilter(@InformeStockDelegaciF3nCompaF1ia,EqualToCondition,[Informe Stock Delegación].[Compañia],String), FILTER(VALUES('Informe Stock Delegación'[JDFEC]), (vFromInformeStockDelegaciF3nJDFEC1ALL || 'Informe Stock Delegación'[JDFEC] >= DATEVALUE(@FromInformeStockDelegaciF3nJDFEC) + TIMEVALUE("00:00:00"))))