Forum Discussion

SnoekL's avatar
SnoekL
Helper II
6 years ago

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:  

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.

 

DAX
Dimension
Slicer

12 Replies

    • SnoekL's avatar
      SnoekL
      Helper II
      6 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • SnoekL's avatar
      SnoekL
      Helper II
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        OK, I have to build a quick model from your description and show you how to do this.

        Best
        D