Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Problem in selecting Max date

Hello,

 

Can anyone guide me into the right direction here?

 

I need to add a column to a table, that returns the maximum date value from the table, considering also the current filter selection.

 

It works perfectly well, when I add it as a measure, but I cannot seem to figure a way to return this value into the table behind to be used in the creation of another custom column.

 

The setup is pretty basic. A fact table joined to a Dimensional date table.

 

Thanks in advance for any advice.

1 ACCEPTED SOLUTION

HI, @Anonymous

calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.

 

Notice:

1. Calculation column/table not support dynamic changed based on filter or slicer.

(So MAX(table[column]) will return fixed value)
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

 

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
themistoklis
Community Champion
Community Champion

@Anonymous

 

Try the following DAX formula:

 

Column = CALCULATE(MAX('Calendar'[Date]), ALL('Master Table'))

Master Table is my fact table and Calendar is the custome calendar table.

You can change them based on your needs

Anonymous
Not applicable

Hi @themistoklis

 

Thanks for the reply.

 

Unfortunately, this returns the max of the Calendar date table, without taking the active filtering into consideration.

 

date example.PNG

@Anonymous

 

When you say active filtering do you mean filter on dates or on other fields?

 

Can you also share the file with us, and a sample of the desired output?

Anonymous
Not applicable

@themistoklis

 

The filter is done by a slicer currently :).

 

I have created an example file. I'd like the Custom Column (Column_MaxDate) to return the same value as the Measure (Measure_MaxDate) does.

 

You can find the example file in the link below. If prompted a password use 'PBI'

https://transfernow.net/280sp1x9l54t

 

Again thanks for helping 🙂

HI, @Anonymous

calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.

 

Notice:

1. Calculation column/table not support dynamic changed based on filter or slicer.

(So MAX(table[column]) will return fixed value)
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

 

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi  @v-lili6-msft

 

Thank you for the response and clarification - I appreciate that.

 

Best,

Steffen

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.