Return 1 if any row with the same name contains 1 in a particular column

[Murkow]

Hi,

can't think of formula no matter how hard I try

I'll  demonstrate it on an example to make it easier to understand.

I have multiple rows with the same name.

Then I have date. I made a new column where I find out, if the row is from this week (return 1 if it is)

Now I need a New Column, where it retuns 1 to every row, where there is at least one 1 in This week column for that project.

Any ideas?

See the screen below.

Thanks in advance

Project 2 has no 1 in nolumn This week which means it has 0 in every Project 2 row in New Column.

Project 1 and Project 3 have at least one row where there is 1 in Column This week so there is 1 in every Project 1 and Project 3 row in column New Column.

Please, how do I get the New Column formula?

[Eric_Zhang]

@Murkow

You can create to create calculated column as

Column = CALCULATE(MAX('table'[This Week]),ALLEXCEPT('table','table'[Project]))

[Murkow]

Thank you everybody, everything was helpful.

I did it by grouping. Created new column in query where I got TRUE/FALSE if it was in this week. Then I duplicated it, grouped by project and, chynged TRUE/FALSE to 1/0 and maxed this new column like Taico advised me.

[ForcaTaico · Accepted solution]

Hey Murkow

Insted of a new colunm you could duplicate your query and do a "Group by" around [Project] and take the max. of [This week]. Then merge the two queries around [Project] and expand the column with the max. of [This week].

Regards Taico

[Murkow]

Hi Taico,

oh I see... that might be the way. But i can't really take the max of This week.

This week is a new created IF column and it doesn't offer me to even pick this column.

Is there any way to add this column to query?

Thanks

[ForcaTaico]

When you do the "group by" only select the column [Project] then you should be able to take the max. of your custom column [This week]. 

[Murkow]

Since I don't have Current Week column in query editor, can somebody think of a formula on my first question?

Or give me an advice on how to creat a new column in query editor, where there should be 1 if it is in current week based on the date of th row?

Thanks

[ForcaTaico]

You could also add an calculated column after the query is loaded.

=IF(CALCULATE(COUNTROWS(Table),FILTER(Table,EARLIER(Table[Project])=Table[Project] && Table[This week]=1))>0,1,0)

...in addition you could add the date criteria in the formular.