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

     

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.