Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
c-mschukas
Regular Visitor

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

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.

9 REPLIES 9
tarunsingla
Solution Sage
Solution Sage

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

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.

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.

 

Capture0614F.PNG

 

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.

 

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

thank you!

 

working on it and more to follow ASAP.

 

thank you.

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.

 

some success...!

 

SelectedColumn = if(('cpq quotesMikeII'[Is_Order_Submitted__c])=TRUE, "WON","pp")
 
more to follow....thank you!

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?

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.