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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.