All Function

[JamesBurke]

Hi all , 

Max Date new 3 = 
    CALCULATE(
        MAX('Date'[Date]),
        FILTER('Date',[Total solar Generated] >= 0.1),
        ALL('Date'), 
        ALL('NGD'[Meter Name (groups)]))
        

I'm looking for when one would be selected it would stay at the 31 st of october , i thought i could achieve this by the all function , from my understanding the all function removes the filter when one is spefically selected ?

I'm using the Max Date in other Measuer to test if the meters are connected by there max end date but it's filtering each mx end date by meter instead of using an overall max end date 

Desired Outcome : 31st October no matter which block has been selected (National Grid Meter Name)

Any help would be appericated . 

Thanks , James. 

[saud968]

Try this

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


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968]

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!

[JamesBurke]

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.

[saud968]

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!

[Anonymous]

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

[rajendraongole1]

Hi @JamesBurke - you can refine your DAX formula by making full use of the all function as below

Max Date Overall =
CALCULATE(
MAX('Date'[Date]),
FILTER('Date', [Total solar Generated] >= 0.1),
ALL('Date'),
ALL('NGD'[Meter Name (groups)])
)

[JamesBurke]

Hi @rajendraongole1 , 

This hasn't seemed to solve the issue , the data is still being filtered by each indivdiual block still. 

Not sure wether it being in the same table akes a difference or not ?