topic Re: Count rows where sum is less than 20 filter context DAX issue in DAX Commands and Tips

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

Anonymous — Fri, 29 Apr 2022 12:31:03 GMT

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.

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

Whitewater100 — Fri, 29 Apr 2022 14:14:29 GMT

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.

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

Anonymous — Fri, 29 Apr 2022 18:24:39 GMT

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.

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

tamerj1 — Sat, 30 Apr 2022 22:27:43 GMT

Hi Anonymous

you may try

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

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

Whitewater100 — Sun, 01 May 2022 13:20:46 GMT

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

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

Anonymous — Fri, 06 May 2022 09:50:28 GMT

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.

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

Anonymous — Mon, 09 May 2022 10:45:29 GMT

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

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

Anonymous — Mon, 09 May 2022 10:46:03 GMT

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

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

Anonymous — Mon, 09 May 2022 10:46:54 GMT

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

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

tamerj1 — Mon, 09 May 2022 10:54:43 GMT

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

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

Anonymous — Mon, 09 May 2022 17:42:32 GMT

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

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

Anonymous — Mon, 09 May 2022 17:44:24 GMT

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.