topic Re: How to give a value to different rows when condition applies on one only in DAX Commands and Tips

How to give a value to different rows when condition applies on one only

hasier — Tue, 27 Dec 2022 08:55:09 GMT

Hi everyone!

I'm facing a problem that I believe should be easy to solve but I don't know which DAX formula to use, the data I have is the following:

What I need to do is to create a column called "inside" that will have a 1 if the today date (2/5/2023 in the example above) is between the Start and End dates for the Phase "open". The problem is that I need to apply that same value for different rows that have the same Name, so the second time "A" appears on the table should also have a 1 on the "Inside" column. Should it be done with Calculate or a Filter?

And then I also need to get the Name of the nearest End date for those projects that have a 1 on Inside, so if for example there would be more projects with a 1, I would need to know which one is more near to the today's date.

Thank you so much in advance for your help.

Re: How to give a value to different rows when condition applies on one only

IIPowerBlog — Mon, 19 Dec 2022 13:34:53 GMT

hi hasier I would create the if clause on a calculated column in the data model. In this way you will have a 1 or a 0 per row. (1)

Then I would create a measure or a calculated column (you will have to see what suits you best) with MAX date to get the latest date.

I hope this helps.

Re: How to give a value to different rows when condition applies on one only

hasier — Mon, 19 Dec 2022 13:56:00 GMT

Hi IIPowerBlog, thank you for your answer!

I have tried that but I'm only able to get a 1 on the row that has the date, and not on the other row that "A" appears. I need to have the value on both to be able to filter it later.

Re: How to give a value to different rows when condition applies on one only

IIPowerBlog — Mon, 19 Dec 2022 14:03:31 GMT

So you want to relate the 1 or 0 to the Name? How do you differnetiate the rows with the same Name?

Re: How to give a value to different rows when condition applies on one only

hasier — Mon, 19 Dec 2022 14:17:48 GMT

Exactly, I want to choose the 1 or 0 based on the column Phase. When the value on that column is "Open" I check if the today's date is between the Start and End. If it is, I want to put a 1 not only on that row but on all the rows that have that same name, that's what I'm not able to do 😞

Re: How to give a value to different rows when condition applies on one only

IIPowerBlog — Mon, 19 Dec 2022 14:23:52 GMT

You will need one more extra column for this. I think you might be able to achieve this with FILTER() =MIN() , check out this post on Microsoft Learn https://learn.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument

Re: How to give a value to different rows when condition applies on one only

hasier — Mon, 19 Dec 2022 17:02:44 GMT

That's what I'm not able to do, I have tried both functions and multiple columns but I don't get the result I need 😞

Re: How to give a value to different rows when condition applies on one only

Anonymous — Fri, 23 Dec 2022 07:40:02 GMT

Hi hasier ,

I created 4 measures to get your expected outputs. Please try:

Flag = var _today= DATE(2023,5,2) return IF(_today>=MAX('Table'[START]) && _today<=MAX('Table'[END]) && MAX('Table'[Phase])="Open",1,0)

Inside = var _flag=SUMX(FILTER(ALL('Table'),[NAME]=MAX('Table'[NAME])),[Flag]) return IF(_flag>=1,1,0)

Diff = var _today= DATE(2023,5,2) var _end=CALCULATE(MAX('Table'[END]),FILTER('Table',[Flag]=1)) return IF(_end=BLANK(),BLANK(), ABS( DATEDIFF(MAX('Table'[END]),_today,DAY)))

Name of Nearest End Date = CALCULATE(MAX('Table'[NAME]),FILTER('Table',[Diff]=MINX(ALL('Table'),[Diff])))

Best Regards,

Eyelyn Qin

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