Forum Discussion

am_i_really's avatar
am_i_really
Frequent Visitor
3 years ago

IF and TIME function

Hi All,

 

I'm looking for a if and time dax statement that gives me the total count of rows in Table[Order Time] that are less than 10am as "breakfast" and greater than 10 as "Lunch".

 

Thank you in advance!

 

"DAX"
@Power BI

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    am_i_really Try:

    Breakfast = 
  VAR __Table = ADDCOLUMNS('Table',"__Hour",HOUR([Order Time]))
  VAR __Result = COUNTROWS(FILTER(__Table,[__Hour] < 10))
RETURN
  __Result



Lunch = 
  VAR __Table = ADDCOLUMNS('Table',"__Hour",HOUR([Order Time]))
  VAR __Result = COUNTROWS(FILTER(__Table,[__Hour] >= 10))
RETURN
  __Result
  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    am_i_really 

     

    breakfast = 
CALCULATE (
    COUNTROWS ( 'fact' ),
    FILTER (
        'fact',
        TIMEVALUE ( CALCULATE ( MAX ( 'fact'[Value] ) ) ) <= TIME ( 10, 00, 00 )
    )
)

lunch = 
CALCULATE (
    COUNTROWS ( 'fact' ),
    FILTER (
        'fact',
        TIMEVALUE ( CALCULATE ( MAX ( 'fact'[Value] ) ) ) > TIME ( 10, 00, 00 )
    )
)