Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Foguete
Helper I
Helper I

Date parameter report builder format

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

1 ACCEPTED SOLUTION
Foguete
Helper I
Helper I

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"))))

View solution in original post

7 REPLIES 7
Foguete
Helper I
Helper I

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"))))

v-binbinyu-msft
Community Support
Community Support

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.

vbinbinyumsft_0-1674539730307.png

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.

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...
 

 

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.

@v-binbinyu-msft 

 

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 ?

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.

Hi @v-binbinyu-msft
Unfortunately no, still the same message. I am also looking into it but cannot find the solution either

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors