cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filtering Results of a Measure

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filtering Results of a Measure

@awitt 

 

You may add the following measure.

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Re: Filtering Results of a Measure

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.

Highlighted
Community Support
Community Support

Re: Filtering Results of a Measure

@awitt 

 

You may add the following measure.

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors