cancel
Showing results for
Search instead 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

Thank you in advance,

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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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

@GeorgeGiannakis , Accepted your LinkedIn connection.

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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

## Helpful resources

Announcements

#### Happy New Year from Power BI

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

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors