Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ortignano
Helper II
Helper II

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

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

 

View solution in original post

5 REPLIES 5
Bibiano_Geraldo
Super User
Super User

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

 

 

 

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

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
Super User
Super User

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
Super User
Super User

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.