topic Re: Row context problem in DAX Commands and Tips

Row context problem

Kebas_Leech — Wed, 21 Jun 2023 11:03:50 GMT

Hello everyone,

I have a daily target measure that calculates the targets for each day of the month based on a fixed monthly target: (the daily target is always the same each day of that specific month).

So i had to calculate the [NumberDaysMonth] and filter it according to the respective holidays of that month.

Total NumberDaysMonth = Var TotalDays = SUMX ( SUMMARIZE ( ADDCOLUMNS ( VALUES ( Tab_Origem[Data] ), "EOMonth", EOMONTH ( Tab_Origem[Data], 0 ) ), [EOMonth] ), DAY ( [EOMonth] ) ) Var NrHolidays = SUMX( SUMMARIZE( Tab_Origem, Tab_Origem[Data], Tab_Origem[Holidays], "Count", COUNTROWS(Tab_Origem) ), IF( Tab_Origem[Holidays] = "Yes", [Count], 0) ) RETURN TotalDays-NrHolidays

My problem is that it calculates on a row filter context based on the holiday day (1st of May). I need all remaining May days to have the same number as the respective lowest value calculated. In my example its 30 so I need all May days to be 30. If it had two holidays it needed to be 29, and so on.

How can I manage that?

Thank you.

Re: Row context problem

some_bih — Wed, 21 Jun 2023 11:15:59 GMT

Hi Kebas_Leech it seems you need to calculate NETWORKDAYS. Check links with examples. Hope this help

https://learn.microsoft.com/en-us/dax/networkdays-dax

https://dax.guide/networkdays/

Re: Row context problem

devanshi — Wed, 21 Jun 2023 12:11:32 GMT

CountHolidays =
VAR holidays = MINX(FILTER('TableName', [Holiday] = True), COUNT('TableName'))
VAR selectedmonth = CALCULATE(COUNTROWS('TableName'), [Month] = SELECTEDVALUE[Month] && [Hloday] <> True)
RETURN
IF([Holiday] = True, holidays, selectedmonth)

Re: Row context problem

Kebas_Leech — Wed, 21 Jun 2023 17:43:43 GMT

Doesnt work. At least for two reasons:

1) I only have listed working days in my database
2) and even if a consider the following code:

Var Firstday = STARTOFMONTH(Tab_Origem[Data]) Var EndDay = ENDOFMONTH(Tab_Origem[Data]) Var HolidayDate = CALCULATETABLE(VALUES(Tab_Origem[Data]), 'Tab_Origem'[Holidays] = "Yes") Var Net = NETWORKDAYS(Firstday, EndDay, 1, HolidayDate)

It does the same mistake/problem that I had in the first post, and that is : Whenever the Tab_Origem[ Holidays] is "Yes" the corresponding date always has one less total day. Using the code i wrote here , the result is:

As you see the May 1st as one less day.

I want, in May , all days to be 30 because i only have 1 holiday in this month and all the other are workingdays.

Re: Row context problem

Kebas_Leech — Wed, 21 Jun 2023 17:52:46 GMT

Couldnt make that measure to work.

[Holidays] is not a measure but a columm -> Tab_Origem[Holidays].
I dont have any measure named [Month] only a data column named Tab_Origem[Data]

Re: Row context problem

Kebas_Leech — Thu, 22 Jun 2023 09:30:29 GMT

Link with the pbix file:

https://drive.google.com/file/d/1XuChGEFZd-GZ2n3jm6AmzqrnomLmHeja/view?usp=drive_link

Result needed: