Measure problem (problem with ALL)

[Ortignano]

Hi,
I have a power by schema with three tables: ALLProducts(with MonthID_open, MonthID_MFG, Qtà fallita and a column model that is related to the FamilyTbl, a calendar table and Family tbl with a column Inverter family.
I would count the overlla quantity by product that have a MonthID_open between 2 dates (last data open -2 and FirstDate_Open) and MonthID_MFG between (FirstDate_MFG and Lastdate_shift_2_Month). The formula that I have is:
VAR LastDateOpen =
MAX ( 'Calendar Lookup'[MonthID] )
VAR Lastdate_shift_2_Month = LastDateOpen - 2
VAR FirstDate_Open = LastDateOpen - 11
VAR FirstDate_MFG = FirstDate_Open - 2
RETURN
CALCULATE (
SUM ( 'AllProducts'[Qtà fallita] )+0,
FILTER (
ALLEXCEPT('AllProducts',FamilyTbl[Inverter Family]),
( 'AllProducts'[MonthID_MFG] >= FirstDate_MFG
&& 'AllProducts'[MonthID_MFG] <= Lastdate_shift_2_Month )
&& ( 'AllProducts'[MonthID_Open] >= FirstDate_Open
&& 'AllProducts'[MonthID_Open] <= LastDateOpen )
)
)

But it account the overall quantity and is not affedted by the slicer Inverter family
Can you help me?
Thank you

I tried to use the formula above but ity doesn't work

[Bibiano_Geraldo]

Hi @Ortignano ,

The issue with your current formula is the use of ALLEXCEPT. While ALLEXCEPT excludes some filters (in your case, FamilyTbl[Inverter Family]), it removes all other filters, including those applied by slicers. This can cause the calculation to ignore slicer selections.

To fix this and ensure the measure respects the slicer for Inverter Family, you can use a slightly modified approach:

VAR LastDateOpen = MAX('Calendar Lookup'[MonthID])
VAR Lastdate_shift_2_Month = LastDateOpen - 2
VAR FirstDate_Open = LastDateOpen - 11
VAR FirstDate_MFG = FirstDate_Open - 2

RETURN
CALCULATE(
    SUM('AllProducts'[Qtà fallita]) + 0,
    FILTER(
        ALL('AllProducts'),
        'AllProducts'[MonthID_MFG] >= FirstDate_MFG &&
        'AllProducts'[MonthID_MFG] <= Lastdate_shift_2_Month &&
        'AllProducts'[MonthID_Open] >= FirstDate_Open &&
        'AllProducts'[MonthID_Open] <= LastDateOpen
    ),
    KEEPFILTERS(FamilyTbl[Inverter Family])
)

[Ortignano]

Thank you, it give me an error as : cannot convert value 'Family1' of type Text to type True/false

[Bibiano_Geraldo · Accepted solution]

Hi @Ortignano ,

See the updated DAX:

VAR LastDateOpen = MAX('Calendar Lookup'[MonthID])
VAR Lastdate_shift_2_Month = LastDateOpen - 2
VAR FirstDate_Open = LastDateOpen - 11
VAR FirstDate_MFG = FirstDate_Open - 2

RETURN
CALCULATE(
    SUM('AllProducts'[Qtà fallita]) + 0,
    FILTER(
        'AllProducts',
        'AllProducts'[MonthID_MFG] >= FirstDate_MFG &&
        'AllProducts'[MonthID_MFG] <= Lastdate_shift_2_Month &&
        'AllProducts'[MonthID_Open] >= FirstDate_Open &&
        'AllProducts'[MonthID_Open] <= LastDateOpen
    ),
    FILTER(
        FamilyTbl,
        FamilyTbl[Inverter Family] IN VALUES(FamilyTbl[Inverter Family])
    )
)

[anmolmalviya05]

Hi @Ortignano, Please try the below formula.

VAR LastDateOpen =
MAX('Calendar Lookup'[MonthID])
VAR Lastdate_shift_2_Month =
LastDateOpen - 2
VAR FirstDate_Open =
LastDateOpen - 11
VAR FirstDate_MFG =
FirstDate_Open - 2
RETURN
CALCULATE(
SUM('AllProducts'[Qtà fallita]) + 0,
FILTER(
'AllProducts',
'AllProducts'[MonthID_MFG] >= FirstDate_MFG
&& 'AllProducts'[MonthID_MFG] <= Lastdate_shift_2_Month
&& 'AllProducts'[MonthID_Open] >= FirstDate_Open
&& 'AllProducts'[MonthID_Open] <= LastDateOpen
),
// Ensure the slicer context is applied
KEEPFILTERS(FamilyTbl[Inverter Family])
)

[rajendraongole1]

Hi @Ortignano -  you can adjust your DAX formula to respect all the active filters, including those applied via slicers.

I have replaced wth allexpect function that you used , you can try with below with filter and all 

VAR LastDateOpen =
MAX('Calendar Lookup'[MonthID])
VAR Lastdate_shift_2_Month =
LastDateOpen - 2
VAR FirstDate_Open =
LastDateOpen - 11
VAR FirstDate_MFG =
FirstDate_Open - 2

RETURN
CALCULATE(
SUM('AllProducts'[Qtà fallita]) + 0,
FILTER(
'AllProducts',
'AllProducts'[MonthID_MFG] >= FirstDate_MFG &&
'AllProducts'[MonthID_MFG] <= Lastdate_shift_2_Month &&
'AllProducts'[MonthID_Open] >= FirstDate_Open &&
'AllProducts'[MonthID_Open] <= LastDateOpen
)
)

I hope it works as per the relationships that your defined in model. please check.