Get Slicer Value irrespective of whether data is there or not in Table Visual

[swap1991]

Hi All,

I have been struggling with a scenario from past two days, the requirement is to get the value of date slicer range selection in Table Visual in respect of From Date and To Date.

In the below snippet, I have a date slicer named payment date, I have two measures defined named as From Date & To Date and below are their definition.

From Date =
var _all_select = MINX(ALLSELECTED(TableName[Payment Date]),TableName[Payment Date])
var _all = MINX(ALL(TableName[Payment Date]),TableName[Payment Date])
return IF(NOT(ISFILTERED(TableName[Payment Date])),_all,_all_select)

To Date =
var _all_select = MAXX(ALLSELECTED(TableName[Payment Date]),TableName[Payment Date])
var _all = MAXX(ALL(TableName[Payment Date]),TableName[Payment Date])
return IF(NOT(ISFILTERED(TableName[Payment Date])),_all,_all_select)

 Now, the problem here is I have missing payment dates in between but the slicer allows user to choose any date. Currently, the slicer selected From Date is 1/4/2022 and To Date is 9/26/2024. The measure in both cases is calculating the nearest From Date which is 02/25/2024 and nearest To Date which is 04/05/2024 and showing it in the report. 

My requirement is display 1/4/2022 in From Date and 04/05/2024 in To Date or whichever date we select in the slicer. The measures should not be bound to data but to slicer range.

Can someone please help?

Thank you.

[v-denglli-msft · Accepted solution]

Thanks for the reply from @Kedar_Pande , please allow me to provide another insight.
Hi @swap1991 ，

Is the Payment Date field in the slicer the same field as the Payment Date field in the table visual?

If they are the same field, is the maximum date of the Payment Date field in the table vision limited by the filter?

In this case you can use the following measures, their values depend on the selection of the slicer.

From Date = CALCULATE(MIN('Table'[Payment Date]), ALLSELECTED('Table'[Payment Date]))

To Date = CALCULATE(MAX('Table'[Payment Date]),ALLSELECTED('Table'[Payment Date]))

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[swap1991]

@Kedar_Pande This measure seems to be not working and I am getting error as "The MIN function only accepts a column reference as an argument."

However, I tweaked the measure to below but still it is taking the nearest value and not the actual slicer selected value.

__FromDate =

VAR SelectedFromDate = CALCULATE(MIN(CashPaymentRegister[Payment Date]),ALLSELECTED(CashPaymentRegister[Payment Date]))

RETURN IF(ISBLANK(SelectedFromDate),BLANK(),SelectedFromDate)

Thank you.

[Kedar_Pande]

@swap1991 , You can try-

From Date =
VAR SelectedFromDate =
MIN(ALLSELECTED(TableName[Payment Date]))
RETURN
IF(
ISBLANK(SelectedFromDate),
BLANK(),
SelectedFromDate
)

To Date =
VAR SelectedToDate =
MAX(ALLSELECTED(TableName[Payment Date]))
RETURN
IF(
ISBLANK(SelectedToDate),
BLANK(),
SelectedToDate
)

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn