Forum Discussion

ITManuel's avatar
ITManuel
Responsive Resident
6 years ago
Solved

DAX Progress calculation

Hi to everybody,   I would like to calculate and visualize a progress curve showing the design progress in this case.   Data base is the following excel Matrix in which the design progress is upd...
  • ITManuel's avatar
    ITManuel
    6 years ago

    Alright.

     

    Thanks for your help.

     

    Br

ITManuel's avatar
ITManuel
Responsive Resident
6 years ago

Hi Daxer,

 

thanks for your quick respone.

 

I do have a date/calendar table, could you please explaind why this shouldn't be connected to the fact table?

 

Also I don't understand the "@Document" part in the Values function. What do I have to insert here?

 

Thank you

 

Best regards

Anonymous's avatar
Anonymous
Not applicable
6 years ago
If you connect the Dates table, you'll immediately see why it shouldn't be connected. Use the code as-is without any changes.
  • ITManuel's avatar
    ITManuel
    Responsive Resident
    6 years ago

    Ok, done. 

    The progress curve with no date table connected looks like:

     

    The progress curve with date table connected looks like:

     

    Why is there such a difference? Also the trend turns negative into September which is not what the input data says.

     

    Can I flatten the curve for the first case?

     

    Br

     

    • ITManuel's avatar
      ITManuel
      Responsive Resident
      6 years ago

      Also tried to do comparison in between target and real progress. 

       

      Strangely I have a negative trend on the real curve and it does not start at "0" with the following data

       

      PDT=ProgressDesignTarget

      PDR=ProgessDesignReal

       

      Best regards

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      ITManuel

      There is a difference because DAX behaves differently when there are relationships between tables and when there aren't. And whether you should or not have relationships depends on what you want to achieve and what makes sense in a given situation. In this case a relationship on date makes no sense because what you're always interested in is not a cross-section through the table with documents based on the currently selected chunk of time but rather you're interested in the status of the selected documents AT THE END OF THE PERIOD IN QUESTION and this is best achieved through the disconnected Dates table.

      I don't know why your graph for the connected table scenario doesn't work but as this is a case which should not arise according to what I prescribed, it's actually not too much of an interest (even though it can be precisely explained with a bit of an unnecessary effort). But I wouldn't dwell on it too much. Just use the disconnected table scenario and you're good to go.
      • ITManuel's avatar
        ITManuel
        Responsive Resident
        6 years ago

        Hi daxer,

         

        I'm afraid, from what I see the calculated curve seems not to be correct. Even not connecting to the calendar table, there is a negative trend in the end of the curve, which is not what the data says, as there is only a positive trend in the data.

        If I put "0%" in the data table before any progress has started, the negative trend is gone. But infact this does not change the progress/data.

        Also one curve start above "0%" but the data does not say that.

        The curves are currently like a stair, how can I get nice looking curves?

         

        I provide the files under the link below.

         

        https://we.tl/t-mJhErC6NJN

         

        Sorry to bother you, and thanks again in advance.

         

        Best regards

         

        Manuel