topic Re: DAX help needed in DAX Commands and Tips

DAX help needed

Enam — Fri, 02 Aug 2024 14:10:10 GMT

Here is the link to a sample pbix file https://drive.google.com/file/d/1J4C3V4A98IwZpQbubTlF6tPWlwXQG2Ir/view?usp=sharing

or to Excel file https://docs.google.com/spreadsheets/d/18SXUttN51bZLLPfKXnV41RN4lQq62fku/edit?usp=drive_link&ouid=103276039085101602984&rtpof=true&sd=true

For every Monday, I want number of « Activity » WHERE « Star_Date » is previous or equal to each specific Monday AND « End_Date» is posterior or equal to each specific Monday.

Excpected result :

Notes :

- Date format is yyyy/mm/dd.

- Monday in the pbix represents next Monday for Star_Date.

Re: DAX help needed

Gabry — Fri, 02 Aug 2024 15:01:57 GMT

Hello Enam

Do you need a measure or it's ok a calculated column?

This is the formula for calculated column

Count of Activitys =

var _start = Classeur[Start_Date]

var _end= Classeur[End_Date]

var _mon= Classeur[Monday]

RETURN

CALCULATE(COUNT(Classeur[Activity]), all(Classeur),Classeur[Start_Date]<=_mon, Classeur[End_Date]>=_mon)

Instead this could be your measure

Count of Activities Measure =

VAR _mon = SELECTEDVALUE(Classeur[Monday])

RETURN

CALCULATE(

COUNT(Classeur[Activity]),

ALL(Classeur),

Classeur[Start_Date] <= _mon,

Classeur[End_Date] >= _mon

)

let me know 😉

Re: DAX help needed

Enam — Fri, 02 Aug 2024 15:06:55 GMT

Hi, thank you it shoul be a measure.

Re: DAX help needed

Gabry — Fri, 02 Aug 2024 15:24:28 GMT

Enam I previously edited the answer and wrote you also the measure

Re: DAX help needed

Enam — Fri, 02 Aug 2024 15:30:54 GMT

Thank you

Re: DAX help needed

Enam — Fri, 02 Aug 2024 16:38:37 GMT

Thank you

Re: DAX help needed

Enam — Fri, 02 Aug 2024 18:50:29 GMT

Hello @Gabry , the total is ok but not the dispatch by Activity even with a slight change to the measure you kindly provided

Count of Activities Measure =

VAR _mon = SELECTEDVALUE(Classeur[Monday])

RETURN

CALCULATE(

COUNT(Classeur[Activity]),

ALLEXCEPT(Classeur, Classeur[Activity]),

Classeur[Start_Date] <= _mon,

Classeur[End_Date] >= _mon

)

Re: DAX help needed

Gabry — Fri, 02 Aug 2024 19:19:17 GMT

I see. In that case I think you need to create another table, a Mondays table, use it in the column. I attach a sample pbix

I think like this can work but I don't know if it's a feasible solution for you. Let me know, maybe I can think of other solutions

Re: DAX help needed

Enam — Fri, 02 Aug 2024 21:49:05 GMT

Thank you so much for your time.

Re: DAX help needed

Enam — Mon, 05 Aug 2024 15:20:04 GMT

Hi Gabry ,

Now the dispacth is ok but the total disappear

Re: DAX help needed

Gabry — Mon, 05 Aug 2024 15:28:35 GMT

I see, adjust the measure like this:

Count of Activities Measure =

VAR _mon = SELECTEDVALUE('Mondays'[Monday])

VAR _ac = SELECTEDVALUE(Classeur[Activity],"Total")

RETURN

IF(_ac="Total",

CALCULATE(

COUNTROWS(Classeur),

ALL(Classeur),

Classeur[Start_Date] <= _mon,

Classeur[End_Date] >= _mon)

CALCULATE(

COUNTROWS(Classeur),

ALL(Classeur),

Classeur[Start_Date] <= _mon,

Classeur[End_Date] >= _mon,

Classeur[Activity] = _ac

)