Forum Discussion
Showing Values Less than Date chosen
Ah, ok, I don't think I understood. Just to clarify, what you want is for items to show up if their created date is before the selected date on the report AND it hasn't been cancelled before the selected date.
Can you confirm if I have that correctly?
Thanks,
Scott
Hi,
Yes you are correct, I used the logic in this forum question
and I came up with three measures which kind of worked, but I will like this measures to be calculated column so that I can use it as a slicer(or is there a way i can convert a measure to a slicer)
1. Measure 15 = VAR selectedDate = IF (HASONEVALUE('PBI PowerBiDataSet'[cancdate]),MIN('PBI PowerBiDataSet'[cancdate])) RETURN IF( MAX('conformed DimDate'[CalendarDate]) > selectedDate,"FALSE","TRUE")
2. Measure 16 = VAR selectedDate = IF (HASONEVALUE('PBI PowerBiDataSet'[Opportunity Close Date]),MIN('PBI PowerBiDataSet'[Opportunity Close Date])) RETURN IF( MAX('conformed DimDate'[CalendarDate]) < selectedDate,"FALSE","TRUE")
3.Measure 17 = IF([Measure 15] && [Measure 16] = "TRUE" ,"PICK","IGNORE")
Column Context
PBI PowerBiDataSet'[cancdate] this column holds the cancelled date if any or it will be like 9999-01-01
conformed DimDate'[CalendarDate] this is the column on the date
PBI PowerBiDataSet'[Opportunity Close Date]
Well, you won't be able to do this as a calculated column - because you're using dynamic slicers, and calculated columns are calculated before you even get in to the front end of Power BI.
I actually ran into an exactly similar situation at work today - someone wants to filter / slice based on a calculation that doesn't work for "pre calculating". I'll update this if we find a nice way to make this work.
Thanks,
Scott
Hi This was the work around I used and it worked, but not clean though as stated by the user as well
https://community.powerbi.com/t5/Desktop/Cannot-use-measure-in-slicer/td-p/166909
Redsilk - I've tested, and I'm not having issues using measures in filters (vs. slicers), this seems to work fine. We're in the process of moving away from slicers altogether - with the new filter pane experience, filters are much nicer.
Can you use a filter instead?
Thanks,
Scott