Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SnoekL
Helper II
Helper II

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
Anonymous
Not applicable

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

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

 

Anonymous
Not applicable

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

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

Best
D

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.

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

Anonymous
Not applicable

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

Best
D

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?

Anonymous
Not applicable

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

Best
D
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors