Forum Discussion

DataHero's avatar
DataHero
Icon for Helper I rankHelper I
1 year ago

DAX Cumulative Active Count with categories

Hi Forum, Im stucking with the calculation of Cumulative Active Count of IT tickets (inquiries). The raw data look like this: INQUIRY_ID TEAM MinActionDate MaxActionDate IA_ACTION ...
Need Help
vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
1 year ago

what is your expected result, please provide what you expect so that its clearly undertood.    are you expecting the answer to be 2 when filter by support?

DataHero's avatar
DataHero
Icon for Helper I rankHelper I
1 year ago

Hi vanessafvg ,
result should be 1 because its the same inquiry_id: 555555

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    1 year ago

    ah ok, why not using distinctcount on the id instead?  

     

    VAR CurrentDate =
        MAX ( Dim_Date[Datum] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Fact_Inquiries[id] ),
            Fact_Inquiries[MinActionDate] <= CurrentDate,
            -- Inquiry started on or before CurrentDate
            Fact_Inquiries[MaxActionDate] >= CurrentDate -- Inquiry ended on or after CurrentDate
        )

    • DataHero's avatar
      DataHero
      Icon for Helper I rankHelper I
      1 year ago

      Hi vanessafvg ,

      a simple DISTINCTCOUNT doesnt work.
      Then DAX would only use 1 row.
      But it should use the 3 rows, with MinActionDate from 1st row and MaxActionDate from 3rd row.
      I think an aggregation is needed but I dont know how to solve it.

      Or am I wrong?


      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi DataHero ,

         

        Is Dim_Date an unrelated date table and did you create a slicer by [Date] column in it?

        Due to I don't know your data model, I will give you some suggestions. You seem want to get min date and max date for each ID. You can try ALLEXCEPT() function.

        Measure = 
VAR CurrentDate = MAX(Dim_Date[Date])
VAR MinActDate = MINX(ALLEXCEPT(Fact_Inquiries,Fact_Inquiries[INQUIRY_ID]),Fact_Inquiries[MinActionDate])
VAR MaxActDate = MAXX(ALLEXCEPT(Fact_Inquiries,Fact_Inquiries[INQUIRY_ID]),Fact_Inquiries[MaxActionDate])
RETURN
CALCULATE(
COUNTROWS(Fact_Inquiries),
FILTER(ALLSELECTED(Fact_Inquiries),
MinActDate <= CurrentDate && -- Inquiry started on or before CurrentDate
MaxActDate >= CurrentDate -- Inquiry ended on or after CurrentDate
))

        If this reply still couldn't help you solve your issue, please share a sample file with us.

        You can also show us more details about the result you want. You can show us a example like when you select date in Dim_Date and you want the measure to return what result. This will make it easier for us to find the solution.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.