Forum Discussion

Sadhikari's avatar
Sadhikari
Frequent Visitor
1 year ago
Solved

DAX Help - Measure based on different time slice

Hi All, Need some help with a calculation based on dataset below. Dataset tables   Orders       Order Date   Order Hour   Ship Date   Ship Hour   Model Relationship Orders[OrderDate]   -...
DAX
powerbi
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sadhikari ,

    Please try using the following DAX formula to create measures.

    Orders Shipped = COUNTROWS('Orders') + 0
    Orders Placed = 
VAR _currentSHIPHOUR = MAX('Ship Hour'[Ship Hour])
VAR _vtable = SUMMARIZE(ALLSELECTED('Orders'),'Orders'[Order Hour],"_Count",COUNT(Orders[Order Hour]))
RETURN
IF(HASONEVALUE('Ship Hour'[Ship Hour]),SUMX(FILTER(_vtable,[Order Hour]=_currentSHIPHOUR),[_Count]),SUMX(_vtable,[_Count])) + 0

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Selva-Salimi's avatar
Selva-Salimi
Solution Sage
1 year ago

Hi Sadhikari 

 

do you really need that much table to calculate your expectations?! I didn't think so.

 

Although there might be some solutions (like using userelationship) but the easiest one that I recommend is to use your order table, Date dimension and a table include 1 to 24 to cover 24 hours. and don't define any relationship between these tables. and just use this measuer:

 

measure shiped_orders := 

var selected_date := selectedvalue ( Date [date])

var selected_hour := selectedvalue ( Hour [hour])

return

calculate (countrows(order) , filter (orders, orders[shipmentdate]= selected_date && orders [shipment hour]=selected_hour))

 

and for the placed orders ....

 

 

measure placed_orders := 

var selected_date := selectedvalue ( Date [date])

var selected_hour := selectedvalue ( Hour [hour])

return

calculate (countrows(order) , filter (orders, orders[order date]= selected_date && orders [order hour]=selected_hour))

 

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

Sadhikari's avatar
Sadhikari
Frequent Visitor
1 year ago

The model i shared is a subset of main model  so for some reason we need the date and hours dimension but took the approach of adding new  disconnected  'Hour' table and used this table to show visual trends.

Thanks for the help and advise

 

calculation that worked for me 

Orders By Order Hours =
CALCULATE(
COUNTROWS('Orders'),
TREATAS(VALUES(Hours[Hour]),'Order Hours'[OrderHour])
)

 

Orders By Order Hours =
CALCULATE(
COUNTROWS('Orders'),
TREATAS(VALUES(Hours[Hour]),'Ship Hours'[ShipHour])
)