Column depending on a measure or slicer.

[BossCo]

Hi everyone! In my model, I have a dimension table with three columns: item, begin date, end date. I have a slicer for the items, with multiple selection allowed. I need to create a slicer for the dates also, where the begin date needs to be the last of the begin dates of the selected items, and the end date needs to be the first of the end dates of the selected items. How could I do? Any help appreciated, I hope it is clear enough, if it's not just let me know. Thanks!

[V-lianl-msft]

Hi @BossCo ,

Based on your description, you want to get the last start date and the first end date of each project, and then use them as slicers.You need to create two calculated columns:

last_start = CALCULATE(

                MAX(Sheet2[Stage Start]),

                FILTER(

                    ALLSELECTED(Sheet2),

                    EARLIER(Sheet2[QuoteID])=Sheet2[QuoteID]))

first_end = CALCULATE(

                MIN(Sheet2[Stage End]),

                FILTER(

ALLSELECTED(Sheet2),

                    EARLIER(Sheet2[QuoteID])=Sheet2[QuoteID]

))

You can refer to the pbix.

Best Regards,

Liang

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

[BossCo]

Hi V-lianl-msft, my problem is slightly different. I hope to make it clearer now.

Project 1	1/11/19	10/12/19
Project 2	5/11/19	8/12/19
Project 3	3/11/19	10/12/19
Project 4	4/11/19	8/12/19
Project 5	1/11/19	9/12/19

Now, I simply have a slicer for the projects, like this:

What I want is a slicer for the dates, also, based on a calendar table, so that I can filter my fact table (sales) for date and project(s).

I created two measures: 

StartMeasure = LASTDATE(Sheet 2[BeginDate])

EndMeasure = FIRSTDATE(Sheet2[End Date])

These measures, of course, vary based on the project slicer, and it's perfect.

I want the date slicer to only show me values going from StartMeasure to EndMeasure, so varying based on the project slicer.

More basically, I would like the date slicer to show me only dates included in each project selected on the project slicer.

Thanks!

[V-lianl-msft · Accepted solution]

Hi @BossCo ,

Unfortunately, measure cannot be used directly as a slicer and cannot use the values of multiple columns in the same slicer.

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

[BossCo]

Ok thanks, I'll create two card visuals with the begin and the end date and a warning:

"Please, for coherent data DO NOT SELECT dates outside of this range".

😂

Thanks!

[JarroVGIT]

You could create Card visuals with the following measure:

MeasureBeginDate = MAX(Table[begindate])

MeasureEndDate = MIN('Table'[begindate])

Should work when you apply filters, it does in my short test:

The table is to show all values in my test and is unaffected by the slicer

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[BossCo]

Hi djerro, thanks for the answer.

Yes indeed, it works for the card visual.

My problem is that I should create another date slicer, based on a calendar table, which only shows me dates from the MeasureBeginDate until the MeasureEndDate, that's the point 🙂

Thanks for the answer, I hope it's more clear now 🙂