cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OfficeSpacePir8
Regular Visitor

Multi-table measure works by itself, but doesn't slice properly

I pull Jira data for a development team to measure team productivity. I have several tables for that, as well as a designated Calendar(Date) table for date relationships across tables. On one of the pages in my report, I have a histogram displaying the average number of tickets completed per developer per month using the following measure:

 

Integrated Per Dev = 
var maxdate = CALCULATE(
    MAX('JIRA-Issues'[Integrated Date]),
    USERELATIONSHIP('Calendar'[Date], 'JIRA-Issues'[Integrated Date]),
    'JIRA-Issues'[Integrated Date]
)
var devCount = CALCULATE(
    COUNTA('JIRA-Developers'[Developer]),
    'JIRA-Developers'[Start Date] < maxdate,
    'JIRA-Developers'[End Date] >= maxdate
)
return
CALCULATE(
    SUM('JIRA-Issues'[Is Integrated]) / devCount,
    USERELATIONSHIP('Calendar'[Date], 'JIRA-Issues'[Integrated Date])
)

 

 This works as expected using a relative date slicer for Calendar[Date] to filter the visual. On the other hand, if I add a dropdown slicer for a field like 'JIRA-Issues'[Team] which groups the tickets by a subset of developers, then the visual displays values based on the total number of tickets in that month rather than the filtered total.
* E.g. The value for March unfiltered equals 3.9 and there are two "Teams"; filtering by one team equals 2.0 and the other eqauls 1.9

Since these add up to the total (as do all other months) it appears that "SUM('JIRA-Issues'[Is Integrated])" is returning the total number of tickets rather than the number of tickets integrated by the target Team. It appears that my filter context is not what I'm expecting, so I just need to know what mistake I'm making.

2 REPLIES 2
OfficeSpacePir8
Regular Visitor

@V-lianl-msft Sorry I read your reply and saw that I couldn't upload files directly, so this got buried in my to-dos. I've uploaded an .xlsx and a sample .pbix using the data here. The visuals I added should quickly show the issue I'm seeing.
WeTransfer Sample Data 

V-lianl-msft
Community Support
Community Support

As you said, the result of measure is affected by the current context. It is difficult to analyze the issue only by description. Can you share a sample pbix after deleting sensitive data?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors