Forum Discussion
All Function
To achieve your desired outcome of always getting the 31st of October regardless of the selected block, you can modify your DAX measure to explicitly set the date to October 31st when a specific condition is met. The ALL function indeed removes filters, but you need to ensure that your measure logic correctly handles the scenario where you want a fixed date.
Here's how you can adjust your measure:
Max Date new 3 =
IF(
HASONEVALUE('NGD'[Meter Name (groups)]),
DATE(2023, 10, 31), -- Replace with the desired fixed date
CALCULATE(
MAX('Date'[Date]),
FILTER('Date', [Total solar Generated] >= 0.1),
ALL('Date'),
ALL('NGD'[Meter Name (groups)])
)
)
Explanation:
HASONEVALUE('NGD'[Meter Name (groups)]): This checks if there is only one value selected in the 'Meter Name (groups)' column.
DATE(2023, 10, 31): This sets the date to October 31st, 2023. Adjust the year if needed.
CALCULATE(...): This part of the measure is used when no specific meter is selected, applying your original logic.
This way, when a specific meter is selected, the measure will return October 31st. Otherwise, it will calculate the maximum date based on your original conditions.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
Is there a way to make this dynamic , the date will not be fixed , currently it is but this is just a trail phase of the data , when moving forward this data will be subject to change everyday.
I'm planning on using it to check daily on the connectivity of the data.
Hi JamesBurke ,
Do you mean that your data is constantly updated? Does the maximum date change? If so, I recommend that you create calculated column. The calculated column calculates the maximum date that meets the criteria in the current data in real time and is not affected by table visual.
Max Date Column = CALCULATE( MAX('Date'[Date]), FILTER('Date',[Total solar Generated] >= 0.1), ALL('Date'), ALL('NGD'[Meter Name (groups)]))Best regards,
Mengmeng Li
To make the date dynamic and ensure it updates daily based on your data, you can modify your DAX measure to dynamically calculate the maximum date based on the current data context. Here's an approach to achieve this:
Max Date new 3 =
CALCULATE(
MAX('Date'[Date]),
FILTER('Date', [Total solar Generated] >= 0.1),
ALL('Date'),
ALL('NGD'[Meter Name (groups)])
)
This measure will dynamically calculate the maximum date where the Total solar Generated is greater than or equal to 0.1, ignoring any filters on the Date and Meter Name (groups) columns.Explanation:
CALCULATE: This function modifies the context in which the data is evaluated.
MAX('Date'[Date]): This part finds the maximum date in the Date table.
FILTER('Date', [Total solar Generated] >= 0.1): This ensures that only dates where Total solar Generated is at least 0.1 are considered.
ALL('Date') and ALL('NGD'[Meter Name (groups)]): These remove any filters on the Date table and the Meter Name (groups) column, ensuring the calculation considers all data.
Dynamic Date Based on Current Data
If you want the measure to always reflect the latest date in your data, you can use the following approach:Max Date new 3 =
CALCULATE(
MAX('Date'[Date]),
FILTER('Date', [Total solar Generated] >= 0.1)
)
This version does not use the ALL function, so it respects the current context and dynamically updates based on the filtered data.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
