Forum Discussion

Foguete's avatar
Foguete
Helper I
3 years ago
Solved

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

  • Foguete's avatar
    Foguete
    3 years ago

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • Foguete's avatar
      Foguete
      Helper I
      3 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.
  • Foguete's avatar
    Foguete
    Helper I
    3 years ago

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