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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors