topic Re: I need help figuring out which ALL() filter logic will produce the results I need in DAX Commands and Tips

I need help figuring out which ALL() filter logic will produce the results I need

djallarii — Thu, 25 Jan 2024 12:21:38 GMT

Source Table
v_campus_volume[name]	v_campus_volume[fiscal_year]	v_campus_volume[volume]
Campus 1	2021	743
Campus 2	2021	573
Campus 3	2021	455
Campus 4	2021	545
Campus 1	2022	566
Campus 2	2022	554
Campus 3	2022	288
Campus 4	2022	516

Dimension Table
dim_campus[name]	dim_campus[sort_order]
Campus 1	99
Campus 2	1
Campus 3	1
Campus 4	1
Network	89

Current Output
dim_campus[name]	dim_date[fiscal_year]	Sum of Total Volume
Campus 2	2021	573
Campus 2	2022	554
Campus 3	2021	455
Campus 3	2022	288
Campus 4	2021	545
Campus 4	2022	516
Network	2021
Network	2022
Campus 1	2021	743
Campus 1	2022	566

Desired Output
dim_campus[name]	dim_date[fiscal_year]	Sum of Total Volume
Campus 2	2021	573
Campus 2	2022	554
Campus 3	2021	455
Campus 3	2022	288
Campus 4	2021	545
Campus 4	2022	516
Network	2021	2316
Network	2022	1924
Campus 1	2021	743
Campus 1	2022	566

Current measure:
Total Volume = CALCULATE ( SUM ( v_campus_volume[volume] ) )

Where I'm at but I can't figure out which ALL combo to use to get it to work right... I've only been able to get a total from the entire dataset to return anything.

Total Volume =
VAR __SelectedCampus =
SELECTEDVALUE ( dim_campus[name] )
RETURN
IF (
__SelectedCampus = "Network",
CALCULATE ( SUM ( v_campus_volume[volume] ), ALL ( dim_campus ) ),
CALCULATE ( SUM ( v_campus_volume[volume] ) )
)

Re: I need help figuring out which ALL() filter logic will produce the results I need

123abc — Thu, 25 Jan 2024 12:33:06 GMT

It looks like you are trying to calculate the Total Volume based on different conditions, and you want to handle the "Network" case differently. To achieve the desired output, you need to adjust the context transition in your DAX formula.

You can use the following modified measure to achieve the desired output:

Total Volume =
VAR __SelectedCampus = SELECTEDVALUE(dim_campus[name])

RETURN
IF (
__SelectedCampus = "Network",
CALCULATE (
SUM ( v_campus_volume[volume] ),
ALL ( dim_campus[name] ),
VALUES ( dim_campus[sort_order] )
),
CALCULATE ( SUM ( v_campus_volume[volume] ) )
)

Explanation:

For the "Network" case, you use CALCULATE with ALL ( dim_campus[name] ) to remove the filter context on the campus name. Additionally, you use VALUES ( dim_campus[sort_order] ) to maintain the sort order in the result.
For other campuses, you simply calculate the sum of the volume without altering the filter context.

This should give you the desired output where the "Network" rows show the sum across all campuses and years.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: I need help figuring out which ALL() filter logic will produce the results I need

Anonymous — Mon, 29 Jan 2024 02:43:52 GMT

Hi djallarii,

I tested your measure and the results are as follows.

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.