Forum Discussion

Ortignano's avatar
Ortignano
Helper II
1 year ago
Solved

Measure problem (problem with ALL)

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

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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])
    )
)

     

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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.

     

  • anmolmalviya05's avatar
    anmolmalviya05
    Super User
    1 year ago

    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])
    )

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
      Ortignano
      Helper II
      1 year ago

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

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        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])
    )
)