cancel
Showing results for
Did you mean:  Post Partisan

## Summarize with ALLEXCEPT

Good Day All,

I have the following formula to calculate Standard Deviation with a virtual table:

``````StdDev_EquipIDRatio Allexcept =
VAR _table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date], [GSIndex],
"_EquipIDRatio",[EquipmentIDRatio])
RETURN
STDEVX.P(_table,[_EquipIDRatio]) ​``````

I want this formula to ignore any Date Filter contexts on my report page (Calendar, Calendar[Date]).

Figured I should use the ALLEXCEPT function, but am unable to get the syntax correct to make it work.

Hoping someone can advise where the ALLEXCEPT function fits into the Summarize statement.  My [TotalVisits] filter is used to eliminate NaN errors.

Thanks much!

1 ACCEPTED SOLUTION  Super User IV

@rsbin - Should be along the lines of:

``````StdDev_EquipIDRatio Allexcept =
VAR _table = SUMMARIZE(FILTER(ALLEXCEPT(GeneralStatistics,...), GeneralStatistics[TotalVisits] <> 0),[Date], [GSIndex], "_EquipIDRatio",[EquipmentIDRatio])
RETURN
STDEVX.P(_table,[_EquipIDRatio])``````

The ... is where you would list your columns that you want to keep filters on.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

2 REPLIES 2  Super User IV

@rsbin - Should be along the lines of:

``````StdDev_EquipIDRatio Allexcept =
VAR _table = SUMMARIZE(FILTER(ALLEXCEPT(GeneralStatistics,...), GeneralStatistics[TotalVisits] <> 0),[Date], [GSIndex], "_EquipIDRatio",[EquipmentIDRatio])
RETURN
STDEVX.P(_table,[_EquipIDRatio])``````

The ... is where you would list your columns that you want to keep filters on.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Post Partisan

Kudos my friend.  Thank you much!

``````StdDev_EquipIDRatio Allexcept =
VAR _table = SUMMARIZE (FILTER(ALLEXCEPT(GeneralStatistics, GeneralStatistics[Date]), GeneralStatistics[TotalVisits] <> 0),
'Calendar'[Date], [GSIndex],
"_EquipIDRatio",[EquipmentIDRatio])
RETURN
STDEVX.P(_table,[_EquipIDRatio])``````

Had to make one little tweak though.  Had to adjust [Date] to 'Calendar'[Date].   Not sure why, but seems to be working

Anyhow,  thanks again for the quick response.

Best Regards, Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  