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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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