topic Re: ALLEXCEPT and ALL function does not work as I expected in DAX in DAX Commands and Tips

ALLEXCEPT and ALL function does not work as I expected in DAX

boraJustmop — Tue, 16 Jan 2024 12:11:11 GMT

Hi Everyone,

I'm trying to use All/Allexcept functions in some calculations for some case scenarios. I was working with Qlik & QlikSense before and this is easly applicable on Qlik but I could not manage to get this done on PowerBI. This may not be applicable on powerbi or I may not achieve to do it. Eveything explained on the last page(ALL & ALL EXCEPT Examples), but I will give summary here again.

(PBIX file : GoogleDriveLink) Page : ALL & ALL EXCEPT Examples

What I'm trying to do is, I want to put a few measure on a table and when I make a selection from a field, I want only some calculations get affected from that selection, and some others don't.

For example, I will put Master Date as dimension and 3 different calculation on the table. I will make a selection from Service Category field and only 1th. measure will be affected from that selection and others don't. Then I will make another selection from Availability field, then 2.th measure will be affected from the selection etc...

I have 4 tables. Appoinment, Marketing, Utilization, Availability. All those 4 tables merged into appointment table.

Measure 1: Calculation gets affected from only determined fields in the all except function.

Test Appointment Qty = CALCULATE(sum(Appointment[m_appointment_qty]) ,ALLEXCEPT(Appointment ,Appointment[dim_country_id] ,Appointment[dim_country] ,Appointment[dim_master_date] ,Appointment[dim_service_category] ,Appointment[dim_promise_met]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" )

Measure 2: Calculation will not be affected from the determined field selections.

Test2 Appointment Qty = CALCULATE( sum(Appointment[m_appointment_qty]) ,ALL(Appointment[data_flag]) ,ALL(Appointment[Availability]) ,ALL(Appointment[dim_day_diff]) ,ALL(Appointment[cleaner_id]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" )

Those calculations works fine if I don't put any dimension into the table but it does not work if I put any dimension. I tried it with and without FILTER function in the calculation but I could not manage this to work with FILTER function. When I put FILTER in dax formula, FILTER function does not allow me to put multiple dimensions to tell the calculation not to affect from any selection.

More detailed explanation and use cases are on the page in PBIX file. I'd be so happy if you see the file and help me to write the correct DAX formula.

Thanks for your support already.

Table 2 Works fine but Table 1 will not be shown if I make any selection.

I attached the uses cases as well.

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

123abc — Wed, 17 Jan 2024 08:20:06 GMT

It seems like you're dealing with a complex scenario in Power BI where you want to control which measures are affected by user selections. The ALLEXCEPT and ALL functions in DAX are powerful tools, but it might require some adjustments in your DAX formulas to achieve the desired behavior.

Without having access to the PBIX file and the specific data model, it can be challenging to provide an exact solution. However, I can offer some general guidance and suggestions based on the information you've provided:

Use of ALLEXCEPT: In your first measure, you are using ALLEXCEPT to remove filters on specific columns. Make sure that the dimensions you want to exclude are correctly specified. It's essential to understand the relationships between tables and dimensions in your model.

DAX MEASURE:

Test Appointment Qty =
CALCULATE(
SUM(Appointment[m_appointment_qty]),
ALLEXCEPT(
Appointment,
Appointment[dim_country_id],
Appointment[dim_country],
Appointment[dim_master_date],
Appointment[dim_service_category],
Appointment[dim_promise_met]
),
Appointment[table_type] = "Appointment",
Appointment[dim_calculation_type] = "Original"
)

Use of ALL: In your second measure, you are using ALL to remove filters on specific columns. Ensure that the columns you are excluding have the desired effect. Also, it's important to understand the context transition that happens in DAX calculations.

DAX MEASURE:

Test2 Appointment Qty =
CALCULATE(
SUM(Appointment[m_appointment_qty]),
ALL(
Appointment[data_flag],
Appointment[Availability],
Appointment[dim_day_diff],
Appointment[cleaner_id]
),
Appointment[table_type] = "Appointment",
Appointment[dim_calculation_type] = "Original"
)

Context Transition: Be aware of how context transition works in DAX. When you place a field in a table or visual, it can affect the context in which measures are evaluated. Ensure that the dimensions you are using in ALLEXCEPT and ALL are providing the correct context for your calculations.
Debugging: Use the DAX Studio or the Power BI Performance Analyzer to debug your DAX formulas. These tools can help you understand the impact of each filter on your measures.

If the issue persists, it might be helpful to see the actual data model and sample data to provide more specific assistance. You can try simplifying your DAX expressions and gradually adding complexity to identify where the issue arises. Additionally, consider using DAX variables to break down complex calculations into smaller, more manageable parts.

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

Anonymous — Wed, 17 Jan 2024 08:24:38 GMT

Hi boraJustmop ,

Try to modify your dax formula:

Test2 Appointment Qty = CALCULATE( SUM(Appointment[m_appointment_qty]), ALL( Appointment[data_flag], Appointment[Availability], Appointment[dim_day_diff], Appointment[cleaner_id] ), Appointment[table_type] = "Appointment", Appointment[dim_calculation_type] = "Original" )

Best Regards,
Adamk Kong

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

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

boraJustmop — Mon, 22 Jan 2024 07:02:53 GMT

Hi,

Recommended solutions did not work. As I attached the screenshot, by availability & dim_day_diff selection, I expect table 1 and table 3 should not be effected.

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

talespin — Mon, 22 Jan 2024 08:51:07 GMT

This I understand

"What I'm trying to do is, I want to put a few measure on a table and when I make a selection from a field, I want only some calculations get affected from that selection, and some others don't."

I am sorry i could not fully understand the details, can you please simplify your requirement. I saw the file, there are many table visuals and measures. Can you please keep one measure for starter and explain what should impact this measure and what should not

(explicitly mentioning whether you are referring to a slicer or attribute in visual. "when I make a selection from a field" - Please explain. )

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

boraJustmop — Fri, 02 Feb 2024 11:52:14 GMT

Basicly , when I make a selection from avaiability field table that based on service category, should not be affected.

AS you see on the SS, availability field selected and TABLE 1 does not show any value but formula includes

ALL (availability) etc.

Thanks for the efford 🙂

Re: ALLEXCEPT and ALL function does not work as I expected in DAX

talespin — Sat, 03 Feb 2024 19:49:44 GMT

hi boraJustmop ,

If I understand correctly, I don't think measure is the problem.

You have interaction enabled between slicer and table visual. Disable the interaction and that way you can control which slicer impacts which visual.