Help with a max condition

[MCacc]

Hello, 

I have a table with the following information

The columns Code and Info are what I want to show in my visual

Quarter column corresponds to a field from a calendar table with which I create a relationship:

Fact Table N --> 1 Calendar Table

This quarter column from my calendar table is also a filter 

Then, what I'd like to achieve in my tablix in powerBI is this: 

I want to show ONLY the row with the Max MONTH for each quarter selected. The filter is multiple selection. 

I tried both a calculated column with MAX and MAXX but it doesn't work.

Do you have any other ideas?

Thank you!

[Anonymous · Accepted solution]

Hi @MCacc ,

You could create a calculated column in table:

ismax = 
var max_date = MAXX(ALLEXCEPT('Table','Table'[QUARTER]),'Table'[MONTH])
return
IF('Table'[MONTH]=max_date,1,0)

Then add it to visual filter and set value = 1.

Best Regards,

Jay

[NikhilChenna]

Hi @MCacc , I think below is the solution for you.

1. Create a summarize table in power bi , select the modelling tab and select the new table option.

Summarize_table=

SUMMARIZE(

    'table1',

    'table1'[QUARTER],

    "MaxMonth",MAX('table1'[MONTH]),

    "Maxcode",MAX('table1'[CODE]),

    "MaxInfo",MAX('table1'[INFO])
)

this will solve your issue.

Regards, 

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!