the two date values form a Slicer i have on a Canvas into a DAX formula

[c-mschukas]

i would like to reference the two date values form a Slicer i have on a Canvas into a DAX formula (new column)...?

• notes inlcude:
  • user selects start and stop date (calendar picker and/or slider)
  • dates are used in a formula for the values in a  calculated colum

thank you.

[tarunsingla]

PowerBI does not support dynamic Calculated Columns (meaning, the values of CalculatedColumns cannot change once set, based on slicer values, etc.). You would need to create a Calculated Measure instead, to capture the selected value. And use that calculated measure in your DAX.

More details here:

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

[c-mschukas]

thank you.

i'm OK using the SelectValue function, but i don't know how to reference the Slicer and each of the two dates listed in the object's text boxes...?

???

thank you.

[tarunsingla]

Each slicer uses one field from the data model. In case your case, you have two slicers for two date fields. And you can use SELECTEDVALUE(YourTableName[YourDatefield]) to get the selected value of the slicer.

[c-mschukas]

thank you.

in my Slicer object, there are two text fields from one field.

i would like to use these Slicer text boxes as Start and End dates (this works fine for filtering).

but i'm not referencing these dates in my DAX formula?

       SlicerValue = MAX('cpq quotes'[CreatedDate],TODAY())

???

thank you.

[tarunsingla]

In this case, you would need two measures one for start date and one for end date. Something like this:

SelectedStartDate = MINX(ALLSELECTED(Table1[CreatedDate]), Table1[CreatedDate])

SelectedEndDate = MAXX(ALLSELECTED(Table1[CreatedDate]), Table1[CreatedDate])

[c-mschukas]

thank you!

working on it and more to follow ASAP.

thank you.

[c-mschukas]

good news, i can reference the two dates in the Slicer (thank you!).

now, i'm trying to get the date range between these two (to use in an If statement) and i get following error:

Error Message:
     MdxScript(Model) (6, 44) Calculation error in measure 'cpq quotesMikeII'[SelectedBetween]: A date column                           containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.

using:

SelectedBetween = DATESBETWEEN(('cpq quotesMikeII'[CreatedDate]),'cpq quotesMikeII'[SelectedStartDate],'cpq quotesMikeII'[SelectedEndDate])

???

thank you.

[c-mschukas]

some success...!

SelectedColumn = if(('cpq quotesMikeII'[Is_Order_Submitted__c])=TRUE, "WON","pp")

more to follow....thank you!

[c-mschukas]

thank you.

the good news is the function doesn't error out.

SlicerValueMin = MINX(ALLSELECTED('cpq quotes'[CreatedDate]), 'cpq quotes'[CreatedDate])

the bad news (question) is that i only retrieve the False condition (not the True)...???

n.b., i did this with a New Column. is this correct?