Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a question.
Final Objective
To warn users if they select dates beyond the available date range in my table. The idea is to capture date slicer "start" and "end" values to determine if users select dates beyond the available date range scope.
Table
Basic calendar table from 1-Jan-21 to TODAY()
DAX
Date Harvest Min =
CALCULATE(
MIN( '. Master Cal'[Date] ),
ALLSELECTED( '. Master Cal'[Date] )
)
Date Harvest Max =
CALCULATE(
MAX( '. Master Cal'[Date] ),
ALLSELECTED( '. Master Cal'[Date] )
)
Use case
In date picker, select 1-Nov-10 as start value and 20-May-31 as end value.
Those DAX return 01/01/21 and 11/05/21, which are the start and end dates of my table (the min and max dates).
I want to have DAXs that return 1-Nov-10 and 20-May-31.
Anyone knows the answer for the correct DAX? Or any answers, that can achieve the final objective, whatever the approach is.
Any help will be greatly appreciated! Thank you.
Solved! Go to Solution.
Hi @gggar
If you just choose data out of your range in your slicer(between), PBI won't show an error message or any response to you. It will only show default max date and default min date in your data model.
But you can create a measure to get some message warning.
Firstly, we need to create a calendar table with a large enough range. (Because the date you choose should be in data model.) Then we build a slicer by date column in this table.
Slicer = CALENDAR(DATE(2000,1,1),DATE(2100,12,31))
Relate Slicer table and .Master Cal table by Date column.
Then build a measure as below.
Warning =
VAR _End =
MAX ( Slicer[Date] )
VAR _Start =
MIN ( Slicer[Date] )
VAR _Range =
VALUES ( '. Master Cal'[Date] )
VAR _Result =
SWITCH (
TRUE (),
_Start
IN _Range
&& NOT ( _End IN _Range ), "Warning : End out of Range",
NOT ( _Start IN _Range )
&& _End IN _Range, "Warning : Start out of Range",
_Start
IN _Range
&& _End IN _Range, "All in Range",
"Warning : All out of Range"
)
RETURN
_Result
Result is as below.
If you select 1-Nov-10 as start value and 20-May-31 as end value.
It will show a warning to you. You can change the text in measure to show the warning you want.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello I have a problem when I want filter calculations according to end date of slicer. I used
Hi @v-rzhou-msft ,
Thank you for the answer.
Yes am aware of that. Thanks.
I guess there's no way PBI can show an error message or some response if out of range dates are chosen.
Hi @gggar
If you just choose data out of your range in your slicer(between), PBI won't show an error message or any response to you. It will only show default max date and default min date in your data model.
But you can create a measure to get some message warning.
Firstly, we need to create a calendar table with a large enough range. (Because the date you choose should be in data model.) Then we build a slicer by date column in this table.
Slicer = CALENDAR(DATE(2000,1,1),DATE(2100,12,31))
Relate Slicer table and .Master Cal table by Date column.
Then build a measure as below.
Warning =
VAR _End =
MAX ( Slicer[Date] )
VAR _Start =
MIN ( Slicer[Date] )
VAR _Range =
VALUES ( '. Master Cal'[Date] )
VAR _Result =
SWITCH (
TRUE (),
_Start
IN _Range
&& NOT ( _End IN _Range ), "Warning : End out of Range",
NOT ( _Start IN _Range )
&& _End IN _Range, "Warning : Start out of Range",
_Start
IN _Range
&& _End IN _Range, "All in Range",
"Warning : All out of Range"
)
RETURN
_Result
Result is as below.
If you select 1-Nov-10 as start value and 20-May-31 as end value.
It will show a warning to you. You can change the text in measure to show the warning you want.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gggar
Your calendar table is from 1-Jan-21 to TODAY().
. Master Cal = CALENDAR(DATE(2021,01,01),TODAY())
So in your data model, there are only values between 2021/01/01 to today.
I think you should use .Master Cal[Date] to build a Slicer( choose Between) as a Date picker.
You see the slicer will show 2020/01/01 to 2021/05/13(today) by default.
In your data model, 2021/01/01 is the min date and today is the max date.
So you change your start date to 2010/11/01 and end date to 2031/05/20, you will only get the min and max date in your data model (2020/01/01 and 2021/05/13 )
You couldn't get 2010/11/01 and 2031/05/20 because they are not in your data model.
If you want to get 2010/11/01 and 2031/05/20 by min and max function in Dax, you should build a calendar table contains them.
. Master Cal = CALENDAR(DATE(2010,11,01),DATE(2031,05,20))
And you can remind your user, if the [Date Harvest Min] and [Date Harvest Max] will only show default min or default max date which are different from the start date or end date they select in Date picker, it means they have choosen dates out of your calender table range.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |