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

Count of weeks where a measure matches a value

Problem background:

  • When an issue is resolved, it gets a resolution date
  • By summing the issues on a per calendar week basis you get the weekly throughput of issues
  • My model needs to calculate throughput dynamically as there are many issues with many teams and issues are not restricted from belonging to more than one team

model.pngTables.png

What I’m trying to do:
I want to create a histogram of weekly throughput. In other words:

  • For a filtered set of data…
    • How many weeks were 0 issues resolved?
    • How many weeks were 1 issue resolved?
    • How many weeks were 2 issues resolved?
    • ...

conceptually, this is the desired output...

histogram.png

What I’ve tried so far:

I have this measure that calculates resolved issues dynamically

resolved_count = 
	CALCULATE(
		DISTINCTCOUNT(issues[issue_key]),
		issues[status]="Done",
		issue_dates[date_type]="Resolved"
	)

 

Then I started trying to dynamically identify the count of weeks, BUT I can’t figure out how to filter this by if the resolved_count is = throughput_value. Maybe I need to go in a completely different direction. 

throughput_weeks_count =
    COUNTROWS(
         ADDCOLUMNS(
               VALUES(issue_dates_calendar[Calendar Week End Date]),
               "throughput",[resolved_count]
         )
    )

 

Normally I would use a filter function (logically, something like below), but I can’t figure out how to use that when I’m not referencing a real table.

FILTER(
      “throughput_weeks_count”,
      “throughput” = throughput_ranges[throughput_value]
       )

 

Any help is appreciated!

1 ACCEPTED SOLUTION

I was able to resolve this by adding a "last day of week" calendar table to my data model then using this measure to calculate the count of weeks. Also added a min/max boundary to the throughput_range table.

 

VAR weeks = 
COUNTROWS(
    FILTER(
        issue_dates_calendar_weeks,
        AND(
            [resolved_count] >= MIN (throughput_ranges[value_min]),
            [resolved_count] <= MAX (throughput_ranges[value_max])
        )
    )
)

 

View solution in original post

5 REPLIES 5

I was able to resolve this by adding a "last day of week" calendar table to my data model then using this measure to calculate the count of weeks. Also added a min/max boundary to the throughput_range table.

 

VAR weeks = 
COUNTROWS(
    FILTER(
        issue_dates_calendar_weeks,
        AND(
            [resolved_count] >= MIN (throughput_ranges[value_min]),
            [resolved_count] <= MAX (throughput_ranges[value_max])
        )
    )
)

 

bcdobbs
Super User
Super User

Can you create a calculated table at load time basically with your syntax...

throughput_weeks_count =

         ADDCOLUMNS(
               VALUES(issue_dates_calendar[Calendar Week End Date]),
               "throughput",[resolved_count]
         )

(SUMMARIZECOLUMNS might be better)

 

That then materialises the count as a column which you can use as filter on your x axis.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

No, can't do that even though that would be a lot easier. The weekly throughput values need to calculate dynamically because they are filterable by team_issue and issue attributes selections.

Alternatively. Take a disconnected table with numbers 0 through 52 (or higher)

Put that on x axis.

Then in your last measure read the value with selectedvalue and wrap your count rows in a filtered version of the dynamic table. Can write the dax tomorrow if you want.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Maybe, not sure I completely follow.

How would that work for date periods that cross a year boundary or extend longer than a single year? Not sure how the weeks match up in that scenario. 

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.

Top Solution Authors