Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure - Countif <= Date

Hello,

In a visualization, I want showing in the y axis for each data series item

  • count of total
  • count of AgeGT14Days
  • count of AgeEqORLt14Days

I have 2 DAX expressions

 

 

AgeGT14Days = COUNTROWS(FILTER(ALL('AssetManagement Open_N2'),'AssetManagement Open_N2'[Created on] < today()-14))

 

 

AgeEqORLt14Days = COUNTROWS(FILTER(ALL('AssetManagement Open_N2'),'AssetManagement Open_N2'[Created on] >= today()-14))

 however for

  • AgeGT14Days
  • AgeEqORLt14Days

I'm getting the sum of all the data series items' counts, not for the specific data series item's count.
The count for the Count Of Notification within the data series item is working correct through.

Any suggestions on a fix?

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Is that you want to calculate how many days are there depend on Min work center?

    the results of two centers are same due to  the function ALL(), it remove all the filter, so the result is the same. my solution is add more detail for the filter.

    Try the following expression:

    AgeGT14Days =
COUNTROWS(
    FILTER(
        ALL( 'AssetManagement Open_N2' ),
        'AssetManagement Open_N2'[Created on]
            < TODAY() - 14
            && [Min work center] = MAX( 'AssetManagement Open_N2'[Min work center] )
    )
)

     

    If i misunderstood what you want, please let me know.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

7 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    4 years ago

    Anonymous 

    create a DimDate Table instead of using the Date column Directly in the DAX 

    and then use below , hope it helps

    AgeGT14Days = COUNTROWS(FILTER(ALL(DateDim),Datedim[Date] < today()-14))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      VijayP 

      Thanks for your help,

       

      Seems I am now getting the count of the DimDate Table instead.

       

      AgeGT14Days = COUNTROWS(FILTER(ALL(DimDate),DimDate[DateKey]< today()-14))

       

       

  • VijayP's avatar
    VijayP
    Community Champion
    4 years ago

    Anonymous 

    Is it solved or still same issue! pleas share some sample data and the desired outcome snapshot so that i can help you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      VijayP 

      It isn't solved.

       

      On the visual I am trying to get

      • AgeGT14Days = Column_AgeGT14Days
      • AgeEqORLt14Days = Column_AgeEqORLt14Days

       

       

       

       

       

       

      Column_AgeEqORLt14Days = if ('AssetManagement Open_N2'[Created on]> TODAY()-14,1,0)
      Column_AgeGT14Days = if ('AssetManagement Open_N2'[Created on]<= TODAY()-14,1,0)

       

       

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        Is that you want to calculate how many days are there depend on Min work center?

        the results of two centers are same due to  the function ALL(), it remove all the filter, so the result is the same. my solution is add more detail for the filter.

        Try the following expression:

        AgeGT14Days =
COUNTROWS(
    FILTER(
        ALL( 'AssetManagement Open_N2' ),
        'AssetManagement Open_N2'[Created on]
            < TODAY() - 14
            && [Min work center] = MAX( 'AssetManagement Open_N2'[Min work center] )
    )
)

         

        If i misunderstood what you want, please let me know.

         

        Best Regards

        Community Support Team _ chenwu zhu

         

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