cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrGlenn
Frequent Visitor

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?

 

CountRows.png

1 ACCEPTED SOLUTION

Hi @MrGlenn ,

 

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.

View solution in original post

7 REPLIES 7
VijayP
Super User
Super User

@MrGlenn 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


MrGlenn
Frequent Visitor

@VijayP 

It isn't solved.

 

On the visual I am trying to get

  • AgeGT14Days = Column_AgeGT14Days
  • AgeEqORLt14Days = Column_AgeEqORLt14Days

MrGlenn_2-1636443246884.png

 

MrGlenn_1-1636443223744.png

 

MrGlenn_4-1636443459745.png

 

 

MrGlenn_3-1636443288236.png

 

 

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

 

 

Hi @MrGlenn ,

 

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.

It works, thanks you.

 

Can you explain how the MAX works in this? 

Hi @MrGlenn ,

 

Its the same as SELECTEDVALUE( ). MAX() return the center name MN10 when the row's Min center is MN10, Min center is SN31, it will change to SN31.

SELECTEDVALUE function - DAX | Microsoft Docs

 

Best Regards

Community Support Team _ chenwu zhu

VijayP
Super User
Super User

@MrGlenn 

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))

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


MrGlenn
Frequent Visitor

@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))

 

MrGlenn_0-1636428418472.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!