topic Re: the two date values form a Slicer i have on a Canvas into a DAX formula in DAX Commands and Tips

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

c-mschukas — Tue, 11 Jun 2019 21:27:04 GMT

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.

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

tarunsingla — Wed, 12 Jun 2019 15:33:51 GMT

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

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

c-mschukas — Thu, 13 Jun 2019 15:47:01 GMT

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.

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

tarunsingla — Thu, 13 Jun 2019 16:53:39 GMT

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.

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

c-mschukas — Fri, 14 Jun 2019 15:25:15 GMT

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.

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

tarunsingla — Fri, 14 Jun 2019 16:47:12 GMT

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])

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

c-mschukas — Sun, 16 Jun 2019 03:34:33 GMT

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?

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

c-mschukas — Mon, 17 Jun 2019 03:33:15 GMT

thank you!

working on it and more to follow ASAP.

thank you.

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

c-mschukas — Mon, 17 Jun 2019 21:20:24 GMT

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.

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

c-mschukas — Tue, 18 Jun 2019 14:25:20 GMT

some success...!

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

more to follow....thank you!