cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Using date parameters in a DAX measure

Hi,

 

I have created a "Date From" and "Date To" parameter which a user chooses from in a filter on a report.  I then have a measure which is supposed to filter using the parameter to sum amounts within this chosen period.  However I get an error message as per below:

 

dax comparison operations do not support comparing values of type date with values of type text

 

The Dax used is below - the paramters are already selected to Tpe "Date" so I can;t understand what is going wrong!  Any help greatly appreciated!

 

Answer = calculate(sum('Data'[Amount]),filter('Data','Data'[Date]>=values('Parameter From'[Period From])&&'Data[Date]<=values('Parameter To'[Period To])))

2 ACCEPTED SOLUTIONS

hi @Anonymous 

For your requirement, there is another simple way for you to refer to:

Step1:

Create a date table, then create a relationship with "Data" table by date column. Keep the relationship is Inactive.

1.JPG

Step2:

Then use USERELATIONSHIP Function to create a measure as below:

Answer = CALCULATE(SUM(Data[Amount]),USERELATIONSHIP(Data[Date],'Date'[Date]))

Step3:

Now use [Date] from date table as a slicer in Between mode.

Result:

2.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

View solution in original post

Anonymous
Not applicable

I have utilised "userelationship" with my existing two tables and it seems to run at a reasonable speed.

 

Many thanks for your help, accepting as solution.

View solution in original post

10 REPLIES 10
Super User II
Super User II

Hi, It's an interesting way to use parameters. Normally I would recommend selectedvalues but the problem here is that the parameter may be a text. Did you try to convert the value to date with function like DATEVALUE or CONVERT?

https://docs.microsoft.com/en-us/dax/datevalue-function-dax

https://docs.microsoft.com/en-us/dax/convert-function-dax

 

Hope this helps,

Regards,

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



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

Happy to help!

LaDataWeb Blog


Anonymous
Not applicable

I defined both the parameters' type as a Date, and have added the Datevalue function to the other date field but still get the same error message!  Have also tried SELECTEDVALUE instead of VALUES....

Ok, here is a stupid question but it can work, is your Date[date] really a date instead of a text?

Can you show pictures of the what if parameters? maybe we can replicate that for us and try to help you with the same scenario.



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

Happy to help!

LaDataWeb Blog


Anonymous
Not applicable

 

Hi,

 

The date field in the data is shown below - I see that there is a calender icon so it is a recognised date format.  And then the Parameter screen snip - selected as Date type.  

 

date.PNG

parameter.PNG

This doesn't make sense at all. You are adding parameters on edit query that will ask for them at the moment of loading data and you want them to use it on DAX.

Can you show us the Parameter From table and Parameter To? How are you adding those fields to the page in the report?

Because it seems you have a table there created with the edit query parameter.

 

Regards,



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

Happy to help!

LaDataWeb Blog


Anonymous
Not applicable

Hi,

 

Perhaps I am going about this incorrectly.  What I wanted to achieve was a date field parameter which a user selects from a drop down list which is not in the main tables (disconnected).  The Dax expression would then use this date selection against some logic on the date field within the table.

 

There are requirements for different types of totals to respond differently to the same date selected in the parameter, which is why I can't simply use the date field in the table itself as the drop down list for a user.

hi @Anonymous 

For your requirement, there is another simple way for you to refer to:

Step1:

Create a date table, then create a relationship with "Data" table by date column. Keep the relationship is Inactive.

1.JPG

Step2:

Then use USERELATIONSHIP Function to create a measure as below:

Answer = CALCULATE(SUM(Data[Amount]),USERELATIONSHIP(Data[Date],'Date'[Date]))

Step3:

Now use [Date] from date table as a slicer in Between mode.

Result:

2.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

View solution in original post

Anonymous
Not applicable

Hi,

 

Many thanks to both of you for providing solutions - I think it has worked but it has absolutely killed performance to the point where I can't use the file in a reasonble time to continue the work which needs to be done!

 

 

 

 

Anonymous
Not applicable

I have utilised "userelationship" with my existing two tables and it seems to run at a reasonable speed.

 

Many thanks for your help, accepting as solution.

View solution in original post

Ok, you can try two things. One should be adding a DateTable not related with the model (due to your logic) where the date column is a range slicer. Two, adding two dropdownlist for year, month and day that won't interact between them (edit interactions to avoid them filtering).

With this two ways I think you can find a from and to date selected by the user. With the range you can use MAX MIN date to take it and with the other dropdown you can user SELECTEDVALUES.

 

Hope this helps

Regards,

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



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

Happy to help!

LaDataWeb Blog


Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors