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

     

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