Forum Discussion

MrMike's avatar
MrMike
Helper II
5 years ago
Solved

Power Query M change data type variable to data type date

What is the syntax in (Power Query M / DAX) change data type variable to data type date? I tried using FORMAT but I get error message shown below.

 

Flag1 =
var DateRange = SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) >= FORMAT(DateRange, "General Date") & MAX('Table'[End Date]) <= FORMAT(DateRange, "General Date"), 1, 0)
 

Error Message:

MdxScript(Model) (7, 4) Calculation error in measure 'Table'[Flag1]: 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.

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, MrMike 

     

    I am sorry for the late reply. I modify the data based on your sample data. The pbix file is attached in the end.

    Table(Dates are in mm/dd/yyyy format):

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below.

    Visual Control = 
var _start = SELECTEDVALUE('Table'[StartDate])
var _end = SELECTEDVALUE('Table'[EndDate])
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
var re = 
IF(
    NOT(
        OR(
            _start>_max,
            _end<_min
        )
    ),
    1,0
)
return
re


    Finally you need to put the measure in the visual level filter and use 'Date' column from 'Calendar' table to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    SELECTCOLUMNS returns a table so you can't use it inside FORMAT.  Please show some example data and the result you are looking for.

    Pat

    • MrMike's avatar
      MrMike
      Helper II
      5 years ago

      I created a sample power bi report. I don't see how to attach .pbix file to this thread.

      So here is the example of data and result.

      CourseNameStartDateEndDate

      Math1/1/20212/1/2021
      Science2/1/20213/1/2021
      English3/1/20214/1/2021
      Art4/1/20215/1/2021
      Gym1/1/202112/1/2021

       

      Select a start and end date and all course that are with in that range should show. For example 1/1/2021 to 2/1/2021 should show Math, Science and Gym.

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, MrMike 

         

        I am sorry for the late reply. I modify the data based on your sample data. The pbix file is attached in the end.

        Table(Dates are in mm/dd/yyyy format):

         

        Calendar(a calculated table):

        Calendar = CALENDARAUTO()

         

        There is no relationship between two tables. You may create a measure as below.

        Visual Control = 
var _start = SELECTEDVALUE('Table'[StartDate])
var _end = SELECTEDVALUE('Table'[EndDate])
var _min = MIN('Calendar'[Date])
var _max = MAX('Calendar'[Date])
var re = 
IF(
    NOT(
        OR(
            _start>_max,
            _end<_min
        )
    ),
    1,0
)
return
re


        Finally you need to put the measure in the visual level filter and use 'Date' column from 'Calendar' table to filter the result.

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, MrMike 

     

    You may create a measure as below to see it works.

    Flag1 =
VAR DateRange =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Start Date] ) >= DateRange
            && MAX ( 'Table'[End Date] ) <= DateRange,
        1,
        0
    )

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MrMike's avatar
      MrMike
      Helper II
      5 years ago

      I tried your code and got this error: 

      "MdxScript(Model) (9, 9) Calculation error in measure 'Course'[IsCourseWithInDateRange]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

       

      So I tried using VALUE and I tried FORMAT and both have me syntex errors.