topic Re: DAX historical dimension using slicer in DAX Commands and Tips

DAX historical dimension using slicer

SnoekL — Fri, 06 Mar 2020 14:17:22 GMT

Hi, I'm not sure if the subjects reflects my question perfectly but I want to have a dynamic dimension based on the slicer.

A small example below:

One dimension table with two projects:

On dimension table with historical info on the phase status of the project

A fact table with the cost of the project

I created two measures: one for the amount filtered on the date slicer and one to get the last status (phase) of the project:

Amount m = calculate(sum('Cost fact'[Amount]),filter(Kalender,Kalender[Datum]<=max('Cost fact'[Date])))

Phase (hist) = calculate(max('Project (historical)'[Phase]),filter('Project (historical)','Project (historical)'[Date]=max('Cost fact'[Date])))

This does work correct as long as I am showing the project in the matrix but that is not wat I want

January:

Februari:

The challange I'm facing is when not having the project on the visual. How can I get the amount current phase in total without the project. Currently it would show:

This absolutly makes sense as the measure looks at the max phase which is in this case 2 but it's not what i want to achieve. Please help me.

Re: DAX historical dimension using slicer

Greg_Deckler — Fri, 06 Mar 2020 14:26:51 GMT

Can you instead just use your Phase column in that visual?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: DAX historical dimension using slicer

SnoekL — Fri, 06 Mar 2020 17:31:44 GMT

That would always give the the current (last available) phase but I want it to be dynamic based on the slicer. So the project phase should be the status that is applicable at the date selected in the slicer.

Re: DAX historical dimension using slicer

Anonymous — Fri, 06 Mar 2020 18:13:22 GMT

What do you want to achieve? This is the question...

This sentence:

"The challange I'm facing is when not having the project on the visual. How can I get the amount current phase in total without the project."

makes no or little sense to me.

What should Amount m be when there's no filter on Project code? When there are many projects visible in the current context, there is no current phase since projects can be in different phases at the same time.

Best
D

Re: DAX historical dimension using slicer

SnoekL — Fri, 06 Mar 2020 20:38:38 GMT

What I want to achieve is the total amount on the projects on the phase at the selected period. So if I'm selecting January it should show Phase 1 400 (both projects are on phase 1). If I select Februari the result should be Phase 1 200 (project 1001 total cost is on phase 1) and phase 2 total of 600 (which relates to project 1002). So it should not show the phase on which the cost is booked on the project but the phase it currently relates to:

Result:

January (slicer)

Phase 1 400

Total 400

February (slicer)

Phase 1 200

Phase 2 600

Total 800

Re: DAX historical dimension using slicer

Anonymous — Fri, 06 Mar 2020 20:57:53 GMT

OK, I have to build a quick model from your description and show you how to do this.

Best
D

Re: DAX historical dimension using slicer

Anonymous — Fri, 06 Mar 2020 23:01:53 GMT

Here's the file... https://1drv.ms/u/s!ApyQEauTSLtOgYJRx8jwncx3p4gRnA?e=rL5by4

Best
D

Re: DAX historical dimension using slicer

SnoekL — Sun, 08 Mar 2020 08:54:52 GMT

Thx. I will have a look as soon as I’m behind my laptop

Re: DAX historical dimension using slicer

Anonymous — Sun, 08 Mar 2020 10:26:36 GMT

You'd better be in front of it... 🙂

Best
D

Re: DAX historical dimension using slicer

SnoekL — Sun, 08 Mar 2020 12:44:16 GMT

After struggling behind my laptop I switched to the front 🙂 and I looked at your solution which is exacly what I was seeking. Thank you very much. This will help me for sure.

Re: DAX historical dimension using slicer

SnoekL — Mon, 09 Mar 2020 07:37:35 GMT

darlove, I'm trying to include your solution in my model and I have one more challenge.

I have the phase not in the same table as the costs. The fases are in a dimension table and not in the fact table.

Any idea to overcome this challenge in your suggested solution?

Thanks in advance SnoekL

Re: DAX historical dimension using slicer

Anonymous — Mon, 09 Mar 2020 11:52:35 GMT

You can always get the phase for a row in your fact table from a related dimension using the function... RELATED.

Best
D

Re: DAX historical dimension using slicer

SnoekL — Wed, 11 Mar 2020 09:59:49 GMT

You have helped me very much which is appreciated.

But now I have the next challenge.

What if I would want to show on one slide the values of previous month ,the values of current month (in seperate table) and the movement. I created a slicer for the month and am using a filter on the min date and the max date on two tables but both the tables will show the last value of the phase. How can I have the previous month get to show the value of the phase of that was applicable at the end of previous month?