Filtering Results of a Measure

[awitt]

I have a table with a list of statuses per order. Each record contains the order number, status, and time stamp of that status. I have a single measure TimeDiff that evaluates the time between whatever statuses are selected by the user. What I want to do is aggregate the results based on that TimeDiff measure.

So for example, I could want to know that there were 1,000 orders that went from CON to ALL in a given time frame and want to know what percentage of those orders had a TimeDiff of < 24. 

I know I could filter the visual based on the measure but it wouldn't give me the % of the total number of orders. Basically I'm wanting to write a CALCULATE function with filter based on the measure but PBI wont let you do that. Thoughts?

PBIX File 

[v-chuncz-msft · Accepted solution]

@awitt 

You may add the following measure.

Measure =
DIVIDE (
    COUNTROWS (
        FILTER (
            VALUES ( OrderItemStatusHistory[OrderItemID] ),
            ( NOT ( ISBLANK ( [TimeDiff] ) ) )
                && [TimeDiff] < 24
        )
    ),
    COUNTROWS ( VALUES ( OrderItemStatusHistory[OrderItemID] ) )
)

[rajulshah]

Hello @awitt,

Please try following DAX. I am unable to validate it as I didn't understand the data.

Difference from Status 1 to Status 2 = 
VAR Status1 = SELECTEDVALUE(StatusTable1[Status])
VAR Status2 = SELECTEDVALUE(StatusTable2[Status])
VAR Date1 = CALCULATE (
    MAX ( OrderItemStatusHistory[Datetime] ),
    OrderItemStatusHistory[Status] = Status1
)
VAR Date2 = CALCULATE (
    MAX ( OrderItemStatusHistory[Datetime] ),
    OrderItemStatusHistory[Status] = Status2
)
VAR Difference = DATEDIFF([Date1],[Date2],HOUR)
RETURN CALCULATE(COUNT('OrderDetailWithStatus(HST913DR)'[OrderItemID]),FILTER(OrderItemStatusHistory,Difference<24 && NOT(ISBLANK(Difference))))

Please let me know if this didn't help.