Search code examples
countpowerbidax

How to calculate the number of values of current selection which has repeated in older entries

I have below data model, which has two main fact tables. enter image description here

It has below relationship as well.

  • defect_report[IssueKey] = test_execution_summary[ExecutionDefect]
  • test_cycle_details[cycleID] = test_Execution_summary[CycleID]]

So far I could filter my dataset based on the practice name, project name using slicers and from the latest release version using measures and visual filter. The CycleID column has the IDs starting from 1, where Last cycle ID receive the lower number and older cycles receive the larger numbers, because I am sorting my dataset from executed date in descending order.

enter image description here

Now I need to add another column which gives the count of bugs that has appeared in previous cycles. In this case, the GRR-1009 which is a bug in the second cycle is bug which has occurred in earlier cycle. So I need to have 1 for the second cycle name.

enter image description here

Appreciate if someone can help me to resolve this. Since I am new to Power BI, still it is kind of difficult to think from DAX perspective.

Solution

  • Here's the overall approach. Get a list of the bug IDs for the selected cycle. Use REMOVEFILTERS to remove the cycle context and get the count of IDs of previous cycles that are the same as the current.

    Try (excuse any typos):

    Earlier Bug count = 
  var bugIdsInThisCycle =
    CALCULATETABLE(
      DISTINCT('test_execution_summary'[ExecutionDefect]),
      'defect_report'[Issue Type] = "Bug"
    )
  var thisCycle = MIN('test_cycle_details'[cycleID])
  var result = 
    CALCULATE(
      DISTINCTCOUNTNOBLANK('test_execution_summary'[ExecutionDefect]),
      REMOVEFILTERS('test_cycle_details'),
      'test_execution_summary'[CycleID] > thisCycle &&
      'test_execution_summary'[ExecutionDefect] IN bugIdsInThisCycle
    )

  return COALESCE(result, 0)