Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Final client status by grouping on selected date range

Hi, I am fairly new Power BI user, currently working on interesting piece of DAX logic, which should not cause too much hasle, but I feel like I missing something in my code and cannot find out ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hello all,

    I have found solution to my problem. Here is the code:

     

    CountOfMaxStatus = COUNTROWS(
FILTER (
        TestData;
        TestData[TimeStamp]
            = CALCULATE (
                MAX ( TestData[TimeStamp] );
                FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client]));
                FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp]));
                filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp]))
            )
))

    Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure

     

    Thanks AlB and v-lili6-msft for the help.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks you AlB for the take on this challenge! As you wrote, your measure shows the last status only for the whole period, which is the reason why my approach was to start by grouping the values. I am not sure how to solve this tricky selection part though..

 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

Have you tried to use a Date slicer? It should work with that. It seems to be working for me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    AlBThe measure your provided shows the max status for the whole period. If date slicer de-selects this max interaction, it will disapear and will not get recalculated. Maybe I am bad at explaining what I want to achieve, so let me illustrate - we have a client with 3 interactions:

    ClientInteractionIDDateOldStatusNewStatus
    xyzxyz101.decAB
    xyzxyz203.decBC
    xyzxyz305.decCD

    If the date slicer has selected 12.01 - 12.05, the max interaction in this range is xyz3 and the respective max status = "D", therefore I need the output to be like this:

    MeasureValue
    B0
    C0
    D1

    However, now suppose I adjust date slicer to 12.01 - 12.04 The max interaction in this range is xyz2 and the respective max status = "C", therefore I need the output to look like this:

    MeasureValue
    B0
    C1
    D0

    This is what I am trying to achieve. I think that your measure is close to what I need, but I lack the knowledge to tweak it.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hey Anonymous what's up

    I haven't yet had time to look in detail at your latest post but what is the status on this? Have you made any progress?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi AlB, I haven't had time to work on this recently. Should I happen to find the solution, I will definitely share it.