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

     

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