Disregard first slicer after selecting the second slicer

[Anonymous]

Hi Guys, Im still getting used to DAX and im stuck. I need some help with my visual.

These are the columns i summed for my Visual.

These are the filters. And for each filter i select, i want the corresponding "Sum" formula to calculate for me.

This is what happens. After choosing my second slicer, the values tend to get wrong. Its supposed to have 85% saturation however with my calculation im getting a way smaller number.

This is the calculation i have...I've also tries using the "ALL" and "ALLEXCEPT" function but still didnt get it to work.

Hope there is a way to do this and i look forward to your help.

Thank you all,

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Do you mean that you want to disregard first slicer after selecting the second slicer and disregard first and second slicer after selecting the third slicer?

If so, you must define which slicer is first, second, third in the formula in advance.

Try this formula

Measure = 
SWITCH (
    TRUE (),
        ISFILTERED ( 'Lista De actividades'[Rol] ), CALCULATE (
        SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ),
        ALL ( 'Lista De actividades'[Deparmento], 'Lista De actividades'[Sub-Deparmento] )
    ),
    ISFILTERED ( 'Lista De actividades'[Sub-Deparmento] ), CALCULATE ( SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ), ALL ( 'Lista De actividades'[Deparmento] ) ),
    ISFILTERED ( 'Lista De actividades'[Deparmento] ), CALCULATE ( SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ) ),
   
    
    BLANK ()
)

Best Regards,
Lin

[Anonymous]

Thank you so much Lin! it worked! 🙂

---

@v-lili6-msft wrote:

hi, @Anonymous 

Do you mean that you want to disregard first slicer after selecting the second slicer and disregard first and second slicer after selecting the third slicer?

If so, you must define which slicer is first, second, third in the formula in advance.

Try this formula

Measure = 
SWITCH (
    TRUE (),
        ISFILTERED ( 'Lista De actividades'[Rol] ), CALCULATE (
        SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ),
        ALL ( 'Lista De actividades'[Deparmento], 'Lista De actividades'[Sub-Deparmento] )
    ),
    ISFILTERED ( 'Lista De actividades'[Sub-Deparmento] ), CALCULATE ( SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ), ALL ( 'Lista De actividades'[Deparmento] ) ),
    ISFILTERED ( 'Lista De actividades'[Deparmento] ), CALCULATE ( SUM ( 'Lista De actividades'[Saturaction Actual Sub Dept] ) ),
   
    
    BLANK ()
)

Best Regards,
Lin

---