Forum Discussion
All Function
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.
7 Replies
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!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)])
)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 ?
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
