Prefer Order Time (Hour of the day)

[MudassirAli]

@MAAbdullah47

If I understand it correctly, you need the Highest # of orders that can be filtered by (per day - week - month - year). I have a solution based on the assumption I mentioned.

I added a continuous date table in the model that is prepared by @Melissa

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

Then in the data you provided, separated the Hour from Order Time in Power Query.

You have duplicate Order Numbers but they are generated on a different time frame and different Products are ordered on the same order number that’s why didn’t count duplicate order numbers as ONE instead treated every Order as Unique. Moreover, in the dataset provided, many Order Dates are missing that’s why the slicers are showing Blanks also.

The measure to calculate the Highest # of orders by Hour is

Max Orders by Hour =
VAR vTable =
    CALCULATETABLE (
        ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Hour] ), "@Orders", [# Of Orders] ),
        ALLSELECTED ()
    )
VAR MaxOrders =
    MAXX ( vTable, [@Orders] )
VAR Result =
    IF ( [# Of Orders] = MaxOrders, MaxOrders, BLANK () )
RETURN
    Result

The final result is as follows:

Attaching the PBIX file and source file.

Let us know if this is what you are looking for.

Thanks.

Prefer Order Time Solution.pbix (1.9 MB)