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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors