Forum Discussion

ddpl's avatar
ddpl
Solution Sage
3 years ago
Solved

Calculate Count...

Data...

 

Client IDDate
A101-Aug-22
A202-Aug-22
A203-Aug-22
A304-Aug-22
A305-Aug-22
A306-Aug-22
A407-Aug-22
A408-Aug-22
A409-Aug-22
A410-Aug-22
A511-Aug-22
A512-Aug-22
A513-Aug-22
A514-Aug-22
A515-Aug-22

 

I want a measure to count Client Id for Date>= 10th Aug, 2022 in table visual as shown below...

 

Client IDDateReqirement
A410-Aug-22---1
A511-Aug-22---5
A512-Aug-22---5
A513-Aug-22---5
A514-Aug-22---5
A515-Aug-22---5
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ddpl ,

     

    I think you can try this code to create a measure.

    Count =
IF (
    MAX ( 'Table'[Date] ) >= DATE ( 2022, 08, 10 ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Client ID] ),
            'Table'[Date] >= DATE ( 2022, 08, 10 )
        )
    )
)

    Result is as below.

     

    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.

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    ddpl , Use an after date slicer, Create a date table and join that with your table and create measure like

     

    calculate(countrows(Table), allselected(Date))

     

    or with selected date as 10th

     

    calculate(countrows(Table), filter(all(Date), date[Date] >= maxx(allselected(Date) , date[Date]) ) )

     

     

  • ddpl's avatar
    ddpl
    Solution Sage
    3 years ago

    amitchandak ,

     

    I'm expecting something like below...

     

    Count = CALCULATE( COUNT( Table[Client ID]), FILTER( Table, Table[Date]>=DATE(2022,8,10), ALLEXCEPT( Table, Table[Client ID] ) )
     
    but it won't work.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi ddpl ,

       

      I think you can try this code to create a measure.

      Count =
IF (
    MAX ( 'Table'[Date] ) >= DATE ( 2022, 08, 10 ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Client ID] ),
            'Table'[Date] >= DATE ( 2022, 08, 10 )
        )
    )
)

      Result is as below.

       

      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.

       

      • ddpl's avatar
        ddpl
        Solution Sage
        3 years ago

        Anonymous ,

         

        It worked as expected, Thanks for your sharp DAX.

  • MHTANK's avatar
    MHTANK
    Helper III
    3 years ago

    You can go through Quick measure-> Total for category (filters applied)->

     

    Base Value = Client ID and Category = Date

    and drag in matrix