Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
FlowViz
Helper III
Helper III

Measure filtered by slicer - group results by column value

Hi all,

 

I have a measure within my report which calculates the percentage of time items spent in 'active' states (as determined by what the user selects in the slicer). This gives a flow efficiency percentage Active Time / (Active Time + Wait Time).

 

I'm struggling with the measure for this for each individual WorkItemID, as it is giving the same answer for all, for example for 725:

 

Screenshot 2022-02-21 at 17.00.23.png

 

It should actually be showing 3%, as that was the amount of time spent in 'active' states - 4 / (4 + 38 + 63 + 40) * 100

 

Screenshot 2022-02-21 at 17.00.57.png

 

Anyone have any ideas as to hwo to fix this? Pbix is available here if it helps...

1 ACCEPTED SOLUTION

@FlowViz I think the ALLSELECTED is removing more filter context than intended (plus you should never use that function inside of an iterator).

 

Try something simpler like this as a starting point:

FE =
VAR _active =
    CALCULATE (
        [Time in column (days)],
        FILTER (
            VALUES ( 'WorkItems FlowEfficiency'[Column] ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        )
    )
VAR _all = [Time in column (days)]
RETURN
    _active / _all

View solution in original post

5 REPLIES 5
KNP
Super User
Super User

I had a look at the PBIX file. I don't have time right now to come up with a solution unfortunately, but I'm sure someone will have something for you shortly.

 

I like what you did with the navigation features you've built into the report. Those bookmarks must have taken forever. Especially the light/dark mode. I posted an idea a while back on the ideas site that you might like to vote for.

https://ideas.powerbi.com/ideas/idea/?ideaid=781638b4-9b86-eb11-8ced-281878e6452b

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thank you - yes it did take a while! Have upvoted your idea. Hopefully someone can help me out...

@FlowViz - I may have have put others off answering with my initial relply.

 

If (@BA_Pete@AlexisOlson@parry2k) you have time to look at this issue, the below is the measure in question in the PBIX file linked in the original question.

FE =
VAR _active =
    SUMX (
        FILTER (
            ALLSELECTED ( 'WorkItems FlowEfficiency' ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        ),
        'WorkItems FlowEfficiency'[Time in column (days)]
    )
VAR _all =
    SUMX (
        ALLSELECTED ( 'WorkItems FlowEfficiency' ),
        'WorkItems FlowEfficiency'[Time in column (days)]
    )
RETURN
    _active / _all

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@FlowViz I think the ALLSELECTED is removing more filter context than intended (plus you should never use that function inside of an iterator).

 

Try something simpler like this as a starting point:

FE =
VAR _active =
    CALCULATE (
        [Time in column (days)],
        FILTER (
            VALUES ( 'WorkItems FlowEfficiency'[Column] ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        )
    )
VAR _all = [Time in column (days)]
RETURN
    _active / _all

EDIT: thank you again, this is working just as I want it to 🙂

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors