topic Re: Problem Power BI : merge period in table... in DAX Commands and Tips

Problem Power BI : merge period in table...

sebsenciel — Thu, 01 Dec 2022 18:31:56 GMT

Hello to all,

I can't do the following under powerbi 😞
Can you help me please ?
I will try to detail my problem as clear as possible 🙂

My data model is simple: I have only one table, namely the PROJECT table (see attached diagram)

this table has two columns :
- date column
- project tasks column (values T1 and T2)

Each task is associated with a date.

Ok.

This project was carried out over a period of time between a min and max date
The min date of the project : in green
the max date of the project in red

Target : for each task, I would like to associate the period of the corresponding project, indicating for each date of the period if there was an associated task (yes ==> 1, no ==> 0).
See target on the right !

I tried to merge with a calendar table, but I couldn't do it.

Thanks for your help
Sébastien

Re: Problem Power BI : merge period in table...

amitchandak — Fri, 02 Dec 2022 02:23:00 GMT

sebsenciel , Try a measure like below with date table

calculate(Sumx(filter(Date, Date[Date] >= Minx(filter(allselected(Table), Table[Tache] = max(Table[Tache])), Table[Date]) && Date[Date] <= Maxx(filter(allselected(Table), Table[Tache] = max(Table[Tache])), Table[Date])), if(Date[Date] = Max(Table[Tache]) ,1,0)))

Re: Problem Power BI : merge period in table...

sebsenciel — Mon, 05 Dec 2022 10:30:41 GMT

Hi amitchandak,

thank you for your feedback
I will look at your answer and get back to you if I have any questions,
Thank you

Sébastien

Re: Problem Power BI : merge period in table...

Anonymous — Thu, 15 Dec 2022 09:12:21 GMT

Hi sebsenciel ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table = GENERATE(VALUES(Project[Tache]),VALUES(Calendrier[Calendrier Project]))

(3) We can create a calculated column.

Column = var a=FILTER(Project,[Tache]=EARLIER('Table'[Tache])) var b=SUMMARIZE(a,[Date]) return IF([Calendrier Project] in b,1,0)

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please considerAccept it as the solution  to help the other members find it more quickly.

Re: Problem Power BI : merge period in table...

sebsenciel — Mon, 26 Dec 2022 16:35:53 GMT

Hello v-tangjie-msft,

Thank's a lot for your answer.

very NICE !!!!

have a great holiday season 😉

Best regards

Sebastien