Forum Discussion
Allexpect not working with date range change
Hi everyone,
I am trying to calculate max value for a particular group. Using below Dax
Max value= calculate(Max(value), allexpect (tb1, tb1[group]). Ideally Max value should change as per date range. But it is not working. All values are in same table. Any help appreciated.
Thanks for the reply from parry2k, please allow me to provide another insight.
Hi Abhishekkl ,
Below is a data table created based on the information you provided.
Use the following measure to calculate the maximum value for a particular group.
Max value = CALCULATE ( MAX ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Date] ) :
- Remove filters from the table except for the 'Group' and 'Date' fields.
ALLSELECTED ( 'Table'[Date] )
- Removes the filters from the 'Date' field in the visual. Keep filters from external 'Date' field, such as slicers created by 'Date' field.
The final result is shown below.
When the range of the external 'Date' field changes, the measure will change with it.
But it will not be filtered by the 'Date' field that is in the same visual.Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Abhishekkl I think the measure is returning the value as per dax expression, it will be easier if you share some sample data with the expected output. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Thanks for the reply from parry2k, please allow me to provide another insight.
Hi Abhishekkl ,
Below is a data table created based on the information you provided.
Use the following measure to calculate the maximum value for a particular group.
Max value = CALCULATE ( MAX ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Date] ) :
- Remove filters from the table except for the 'Group' and 'Date' fields.
ALLSELECTED ( 'Table'[Date] )
- Removes the filters from the 'Date' field in the visual. Keep filters from external 'Date' field, such as slicers created by 'Date' field.
The final result is shown below.
When the range of the external 'Date' field changes, the measure will change with it.
But it will not be filtered by the 'Date' field that is in the same visual.Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
