cancel
Showing results for
Did you mean:
Helper II

## MinMax values for non blank values of multiple columns

Hello Community,

I would appreciated it if you could help me with the below question.

I have the below table that has shows the benefits details per year of three projects.

What I want to create is a table that shows the minimum and maximum year per project, based on the non blank values of all three benefit columns.

Input table

 Project Year Benefit1 Benefit2 Benefit3 Project1 2019 435 311 Project1 2020 133 200 141 Project1 2021 281 380 Project1 2022 Project2 2019 367 Project2 2020 298 248 262 Project2 2021 255 305 348 Project2 2022 203 Project3 2019 Project3 2020 462 467 491 Project3 2021 440 478 Project3 2022 470

Output table

 Project MinYear MaxYear Project1 2019 2021 Project2 2019 2022 Project3 2020 2022

George

1 ACCEPTED SOLUTION
Super User IV

@GeorgeGiannakis , Try 2 measures like

Min year =

minx(filter(Table, not(isblank(Table[Benefit1])) || not(isblank(Table[Benefit2])) || not(isblank(Table[Benefit3]))), Table[Year])

max year =
maxx(filter(Table, not(isblank(Table[Benefit1])) || not(isblank(Table[Benefit2])) || not(isblank(Table[Benefit3]))), Table[Year])

Proud to be a Super User!

4 REPLIES 4
Super User IV

@GeorgeGiannakis , Try 2 measures like

Min year =

minx(filter(Table, not(isblank(Table[Benefit1])) || not(isblank(Table[Benefit2])) || not(isblank(Table[Benefit3]))), Table[Year])

max year =
maxx(filter(Table, not(isblank(Table[Benefit1])) || not(isblank(Table[Benefit2])) || not(isblank(Table[Benefit3]))), Table[Year])

Proud to be a Super User!

Helper II

Hello @amitchandak

Your solution worked just fine. Thank you very much for that.

I tried to give you a Kudos and flag your reply as a solution, only to come across the below error.

"Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page. "

Would you have any idea how this can be sorted out?

I also sent you a LinkedIn request, would be glad if you would accept it.

Kind regards,

George

Super User IV

Just refresh and check if you still logged in. If not log in again and try.

Proud to be a Super User!

Helper II

Hi @amitchandak ,

I just gave it another try to flag your input as solution without any success.

Can you please flag it as such?

Kind regards,

George

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!