Max value except if null

[sdlx]

I have one product with different date and I need to have only the maximum date for this product. The specificity is that for some product i have a date showing as "null" and in that case i need the null and not the maximum date.

Would it be a way with a  measure or column to have this ?

Thanks in advance for your help.

[v-eachen-msft]

Hi @sdlx ,

You could try the following measure.

Measure  =
VAR a =
    ADDCOLUMNS ( 'Table', "NULL", IF ( 'Table'[Date] = BLANK (), 0, 1 ) )
VAR b =
    PRODUCTX ( a, [NULL] )
RETURN
    IF ( b = 1, MAX ( 'Table'[Date] ), "" )

[sdlx]

Hello !

Thanks for taking the time to help me.

Would it be a way to use your solution to create a custom column directly in the query editor ?

Thanks in advance for your time !

@v-eachen-msft 

[amitchandak]

Try

maxx(table,table[Date])
maxx(table,table[Date],allexpcept(table[product]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[vivran22]

Hello@sdlx 

I have created a sample data for this:

MAX Date = MAXX(Sheet1,IF(COUNTBLANK(Sheet1[Date]) = 0,Sheet1[Date]))

You should get the following result:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[sdlx]

Hello,

Thanks, it works !

I am noticing now that it would help me if I could have the same result as an addedd colum in the query editor.

In that colum I would have the maximum date for the product if no Null value.

If there is a null value for one line of a product then I would have null for all the line of this product.

Would you have an idea for that ?

@vivran22 @v-eachen-msft  @amitchandak