cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
data_analytics
Frequent Visitor

Display 1 or 0 depending on MAXX() output

Is it possible to check output of MAXX() and display the final measure as 0 or 1 depending on MAXX()>0?

Min Requested Ship Date =CALCULATE(
    MIN(factSales[Requested Ship Date])
    ,ALL('Date')
)
Max Delivery Date = CALCULATE(
    MAX(factCustPacking[DELIVERYDATE])
    ,ALL('Date')
)
measure = MAXX(
    factSalesLine
    ,DATEDIFF([Min Requested Ship Date],[Max Delivery Date],DAY)
    )
Check value of "measure": if -ve then display 0. This seems impossible, since I am applying an IF on a measure. Is there a way to achieve this?
factSales and factCustPacking relationship is M-M, connected by common intermediate dim tables.
 
 
1 ACCEPTED SOLUTION

it seems you are using datediff between ship date and delivery date, if you swap the columns, itll work:

Screenshot 2022-10-29 014844.png

Screenshot 2022-10-29 014855.png

View solution in original post

3 REPLIES 3
data_analytics
Frequent Visitor

Fact table is related to dimItem table on ItemKey (dimItem.ItemKey = factTable.ItemKey, 1 to M). ItemID below is from dimItem.
Each ItemID can have multiple ItemKeys in dimFact.

Fact Table: 
Can the last col be displayed as 0 (if value <=0), or 1 (if >0)?

ITEMIDMax Delivery DateEarliest Ship DateMax Del Dt - Earliest Ship Date
A9/30/20221/1/190054898
B9/26/20221/1/190011930
C9/25/20221/1/19007228
D9/26/20221/1/19004952
E3/12/20203/12/20201
F11/10/202011/10/20201
    
    
 Max Delivery Date = CALCULATE(
    Maxx(dimItem, dimItem[Max Delivery Date]))
 Del Dt Minus Ship Date =
 IF(
     Datediff(factSalesLine[Confirmed Ship Date], factSalesLine[Max Delivery Date], DAY) <0,0,1)

it seems you are using datediff between ship date and delivery date, if you swap the columns, itll work:

Screenshot 2022-10-29 014844.png

Screenshot 2022-10-29 014855.png

eliasayy
Impactful Individual
Impactful Individual

measures can be used in if thats odd can you provide with more details? like some sample data wirh your example. and make sure emasure format is whole number then use if([measure] >= INT(0),1,0)

if this helps, kindly accept as solution and a kudos is appreciated

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors