cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX historical dimension using slicer

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: image.png 

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

 

A fact table with the cost of the project 

 

image.png

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:
image.png
Februari:

image.png

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:

image.png

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.

 

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: DAX historical dimension using slicer

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


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

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: DAX historical dimension using slicer

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.

Highlighted
Anonymous
Not applicable

Re: DAX historical dimension using slicer

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

Highlighted
Frequent Visitor

Re: DAX historical dimension using slicer

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

 

Highlighted
Anonymous
Not applicable

Re: DAX historical dimension using slicer

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

Best
D
Highlighted
Anonymous
Not applicable

Re: DAX historical dimension using slicer

Highlighted
Frequent Visitor

Re: DAX historical dimension using slicer

Thx. I will have a look as soon as I’m behind my laptop
Highlighted
Anonymous
Not applicable

Re: DAX historical dimension using slicer

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

Best
D
Highlighted
Frequent Visitor

Re: DAX historical dimension using slicer

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.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021