topic Re: Selected date in slicer to populate a column in a table? in DAX Commands and Tips

Selected date in slicer to populate a column in a table?

Anonymous — Mon, 14 Mar 2022 16:50:50 GMT

Hello,

I am hoping someone has a great solution for this. Is there a way to pick a date from a slicer and have that populate a column in a table with that date value? So I could then create a datediff calculation off it and another "received" date in the table? I know you can capture the value in a measure, but that doesn't help me when I'm trying to calculate for all the records in my table that have various received dates and then group the days (0-15 and > 15). I want to display a chart that groups the total number of orders in these two categories. A measure will not allow that.

Re: Selected date in slicer to populate a column in a table?

Whitewater100 — Mon, 14 Mar 2022 19:03:12 GMT

Hello:

There's several ways to group or bin the data. If you can share your data model(sample) and expecteed result, I beleive there is a way to separate those two groups in a visual.

Re: Selected date in slicer to populate a column in a table?

Anonymous — Mon, 14 Mar 2022 20:30:06 GMT

I have put the link to the sample down below. I would like the date chosen in the slicer to populate a column in the sample table so I can calculature the difference between the date received and selected date. Then bucket those days by 0-15 and >15.

https://drive.google.com/file/d/1HabW0zMpDNeqSEMU0nKoSBRn47_u01kZ/view?usp=sharing

Re: Selected date in slicer to populate a column in a table?

Whitewater100 — Tue, 15 Mar 2022 00:04:51 GMT

Hi Kristisabo:

The issue calculate columns/tables are computed during database loading and then stored in the model, they do not respond to selections on the Slicers.

So it is not possible to create a calculate column/table can change dynamically with user selections on the Slicer.

We can definitely count the occrances of the two status's.

Count < 15 = CALCULATE(COUNTROWS(Data), FILTER(ALL(data),

Data[Open Category] = ">15")

)

Count 0- 15 = CALCULATE(COUNTROWS(Data), FILTER(ALL(data),

Data[Open Category] = "0-15")

)

If you has the other date for each row it would be no problem o figure out the service level days and after than they could be bucketed with < 15 days and over 15 days, but with ship date/ fulfillmet date we can even do that.

I beleive if you get both dates for each transaction, your request can be handled.

That's the best I know for now.

Thank you.

Re: Selected date in slicer to populate a column in a table?

Anonymous — Tue, 15 Mar 2022 13:54:04 GMT

Thank you so much for your help. However I needed to be able to calculate the number of days from received to date selected and then have a column where the category "0-15 or >15" would be filled in. And then tally those up. Sorry, I didn't explain it well. The column in which I provided in the sample containing the category was for providing what I wanted the end result to look like.

Re: Selected date in slicer to populate a column in a table?

V-lianl-msft — Thu, 17 Mar 2022 07:51:36 GMT

Hi Anonymous ,

Try to create measures for each group, and then manually enter a disconnected group table. Please refer to:

G_>15 = var value_ = IF(ISFILTERED(Dates[Dates]),SELECTEDVALUE(Dates[Dates]),TODAY()) return CALCULATE(COUNT('Sample'[Order #]),FILTER('Sample',ABS(DATEDIFF('Sample'[Date Received],value_,DAY))>15))

G_0-15 = var value_ = IF(ISFILTERED(Dates[Dates]),SELECTEDVALUE(Dates[Dates]),TODAY()) return CALCULATE(COUNT('Sample'[Order #]),FILTER('Sample',ABS(DATEDIFF('Sample'[Date Received],value_,DAY))<15))

Measure = SWITCH(TRUE(), SELECTEDVALUE('Table'[Open Category])=">15",'Sample'[G_>15], SELECTEDVALUE('Table'[Open Category])="0-15",'Sample'[G_0-15])

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

Re: Selected date in slicer to populate a column in a table?

Anonymous — Thu, 17 Mar 2022 16:21:19 GMT

But how do I configure the days between the Date Received and the Selected Date value in the slicer?

Re: Selected date in slicer to populate a column in a table?

V-lianl-msft — Fri, 18 Mar 2022 02:40:09 GMT

Hi Anonymous ,

What fields do you need to use in the slicer? I don't understand. Please provide the results you expect based on your sample data

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

Re: Selected date in slicer to populate a column in a table?

Anonymous — Fri, 18 Mar 2022 13:16:28 GMT

Hello Liang,

I have a date table that I use to populate the date slicer. I want to select a date from the slicer and then have a calculated column that would determine the number of days between the date received for each record in the sample data table and the date selected from the slicer. This will return a different number for each record because not all records were received on the same date. Then based on the value it returns, it would categorize it as under 15 days "0-15" or over 15 days ">15".

Does that make sense? I hope I am doing a good job explaining. Sorry if this is confusing!