Distinct Count of items from second distinct count

[GregCet]

Hello,

I have searched through the forum and cannot find anything that matches what I am trying to do (although I am sure it's there somewhere). I also feel this should be a fairly simple measure to write but am completely struggling with it. Please help!

Data
I have merged 3 tables which are coming from Jira. These provide a list of Issues and I have merged this to incorporate Sprint data and also Changelog Data. So I am now working with 1 single table however it means that there are duplicate rows for a lot of Issues, especially if the Issue has multiple changes applied to it and/or has been registered one more than one Sprint. For this reason when I am doing counts of the issues I must use DISTINCTCOUNT. This is working fine for simple filtered counts (such as filtering Issues to count number of Stories).

Problem

I am trying to calculate Issues that have been registered into multiple Sprints. I can see which Issues have had multiple sprints simply by doing a simple IF statement:

Multi Sprint Issues = IF(DISTINCTCOUNT('Jira Merged'[Sprint Key])>1,1,0)

However, I would like to show a count of these Multi Sprint Issues on a card and of course with this statement the card will only show '1'.

I thought that I would be able to use a filtered expression to achieve what I am looking for but the following expression only returns the total count of all issues in my table:

Multi Sprint Issues = 
CALCULATE(
    DISTINCTCOUNT('Jira Merged'[id]),
    FILTER('Jira Merged',DISTINCTCOUNT('Jira Merged'[Sprint Key])>1)
)

Can anyone support with the best way to achieve what I am looking to do here?

Thanks!

[amitchandak]

@GregCet , Try a measure like

CALCULATE(
countx(Filter(values('Jira Merged'[id]),calculate(DISTINCTCOUNT('Jira Merged'[Sprint Key]))>1),[id])
)

[GregCet]

Hello,

Thanks for your quick response!

I have tried your measure however it still gives the output as the total distinct count of issues from my table (688). I have manually counted the outcome that I should be getting from using the IF statement measure and it should be 81, so this is the value that I am 'searching' for.

Interestingly, with your measure, I amble to change the '>1' value to anything between 1-10 and still get 688 however is I change it to 11 or over then i get '(Blank)' as the output.

[amitchandak]

@GregCet , Is it possible to share a sample data?

Also check like this

measure  =

Var _cnt =calculate(DISTINCTCOUNT('Jira Merged'[Sprint Key]))

return

CALCULATE(
countx(Filter(values('Jira Merged'[id]), _cnt >1),[id])
)

[GregCet]

@amitchandak The outcome was the same as the previous measure.

I can provide a table with example data but am unsure how to attach it here.

[Icey · Accepted solution]

Hi @GregCet ,

I create a simple sample, please check if this is what you want:

Multi Sprint Issues =
CALCULATE (
    DISTINCTCOUNT ( 'Jira Merged'[id] ),
    FILTER (
        'Jira Merged',
        CALCULATE (
            DISTINCTCOUNT ( 'Jira Merged'[Sprint Key] ),
            ALLEXCEPT ( 'Jira Merged', 'Jira Merged'[id] )
        ) > 1
    )
)

If not, please refer to this post to provide sample data:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community.

Best Regards,

Icey

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

[GregCet]

This worked perfectly! Thank you so much!

[GregCet]

@amitchandak 

Any other suggestions for this? I can provide some test data if that helps.