Count rows where sum is less than 20 filter context DAX issue

[Anonymous]

Hi Guys,

I am facing an issue while creating a column "Asset<20h" which count the number of assets which have worked less than 20 hours. 

I have date and branch as my external filters and I need to show this count on a card as in the screen shot:

The card should show "3" as there are three assets with less than 20 hours for these filters. This is my calculated column:

Asset<20h = CALCULATE(DISTINCTCOUNT(ByAsset[Asset Number]),FILTER(ALLEXCEPT(ByAsset,ByAsset[Asset Number],ByAsset[Branch],ByAsset[Date]),sum(ByAsset[Total hours])<20 ))

Powerbi file is here: https://drive.google.com/file/d/1b1n62BrcZLfr51Q84n3fxlOFvmUvYahD/view?usp=sharing

Your help is requested.

@tamerj1 And All.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you can get result directly by this code.

Measure:

Asset<20h =
VAR _TOTALHOURS =
    SUMMARIZE (
        ALLSELECTED ( ByAsset ),
        ByAsset[Asset Number],
        "Total Hours", CALCULATE ( SUM ( ByAsset[Total hours] ) )
    )
RETURN
    COUNTAX ( FILTER ( _TOTALHOURS, [Total Hours] < 20 ), [Asset Number] )

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.

[Anonymous]

Hi Rico, apologies for delayed response as holidays were in progress here ,

thank you for your response, you solution works good, but if I do a multi select filter like choose many branches the sum goes off, I have a requirement for showing total hours for multiple branches too, can you assist, I just tried, it should gives us "4":

Asset<20h =
VAR _TOTALHOURS =
SUMMARIZE (
Filter(ByAsset,ByAsset[Branch] in {"Naval Base","Boddington"}),
ByAsset[Asset Number],
"Total Hours", SUM ( ByAsset[Total hours] )
)
RETURN
COUNTAX ( FILTER ( _TOTALHOURS, [Total Hours] < 20 ), [Asset Number] )

[Anonymous]

Hi Rico, I got it to work by just summing them, thank you again for your assistance:

Asset<20h_All = 
VAR _TOTALHOURS_NAVALS =
SUMMARIZE (
Filter(ByAsset,ByAsset[Branch] in {"Naval Base"}),
ByAsset[Asset Number],
"Total Hours Navals", SUM ( ByAsset[Total hours] )
)
VAR _TOTALHOURS_BODDINGTON =
SUMMARIZE (
Filter(ByAsset,ByAsset[Branch] in {"Boddington"}),
ByAsset[Asset Number],
"Total Hours Bod", SUM ( ByAsset[Total hours] )
)
VAR _TOTALHOURS_PH =
SUMMARIZE (
Filter(ByAsset,ByAsset[Branch] in {"Port Hedland"}),
ByAsset[Asset Number],
"Total Hours PH", SUM ( ByAsset[Total hours] )
)
RETURN
COUNTAX ( FILTER ( _TOTALHOURS_NAVALS, [Total Hours Navals] < 20 ), [Asset Number] )+ COUNTAX ( FILTER ( _TOTALHOURS_BODDINGTON, [Total Hours Bod]< 20 ), [Asset Number] )
+COUNTAX ( FILTER ( _TOTALHOURS_PH, [Total Hours PH]< 20 ), [Asset Number] )

[tamerj1]

Hi @Anonymous 

you may try

Asset<20h =
SUMX (
    SUMMARIZE ( ByAsset, ByAsset[Asset Number], ByAsset[Date] ),
    CALCULATE ( IF ( SUM ( ByAsset[Total hours] ) < 20, 1 ) )
)

[Anonymous]

Hi tamerj1, thank you for your response, but this gives quite different number.

[tamerj1]

Hi @Anonymous 
Just now I saw your clarification to Rico.

You can try 

Asset<20h =
SUMX (
    SUMMARIZE (
        FILTER ( ByAsset, ByAsset[Branch] IN { "Naval Base", "Boddington" } ),
        ByAsset[Asset Number],
        ByAsset[Date]
    ),
    CALCULATE ( IF ( SUM ( ByAsset[Total hours] ) < 20, 1 ) )
)

[Anonymous]

Thank you tamerj1, 
The number is off by sumx but summing up Rico logic works great, Thank you so much for your assistance , closing this thread now.

[Whitewater100]

Hi:

I think with all of your data, the best approach is for you to have a data model. I have created one for you and will attach it here. I noticed a number of entries with no Asset assigned but date and hours. I provided meausres not calculated columns as the calc column is not a good practice for the answers you want.

I hope this solves your question and positions you better to do more complex analysis down the road. I think you want assets 20 hours or more, as that figure ties into Naval for July 2021. You will notice separate unique tables for dates, branches and assets. These are the tables to pull fields from for your visuals. You can actually deleted branch name and asset name from your main table, but I have left them in so you can see what is occuring. I had to download and bring in your Table to get access to make these updates.

https://drive.google.com/file/d/1d3BB5zZN8d8hdumLfdYLt-80cMNqaKlM/view?usp=sharing 

I hope this solves your question.

[Anonymous]

Thank you so much WhiteWater for your time but the issue did not resolve currently,

May be you understand my req incorrectly, basically I want to count number of assets which has worked less than 20 hours for any particular branch and date range.

so in this screenshot for Date range 1-31 july 2021 , you can see raw data in the table , the first three rows  have total hours less than 20 as highlighted, while others have more than 20 ,  so the card "Asset <20 hours" should show count of "3" rather than 986 currently. Let me know if any confusion.

[Whitewater100]

Hi:

That was a challenge because to get the answer you want you seem like it is a monthly figure, not daily to be used with a day slicer. I was stuck on 986 no matter what approach used. Here is a version to arrive at the monthly assets < 20, with Naval Base in July 2021 with an answer of 3.  I hope this is what answer you are looking for.

Assets <20h (MO) = SUMX ( SUMMARIZE ( AssetFactTable,AssetFactTable[Asset Number], AssetFactTable[Month] ), CALCULATE ( IF ( SUM ( AssetFactTable[Total hours] ) < 20, 1, 0 ) ) )

https://drive.google.com/file/d/1ou-HRUaKqFuslTxWuo8g_lC4YC2jAvTj/view?usp=sharing 

[Anonymous]

Thank you , let me try this too , will get back