DAX measure (distinct count of column but with a measure filter applied on it)

[thinker_02]

I’m working with a Project Management dataset where I’m tracking issues and their severity across different project milestones. For each Project ID (ProjectID_PM), I have a Milestone Date (Project_Milestone_Date) and the Severity of Issues (Issue_Severity_PM), which ranges from "High" to "Low".

### Goal:
I’m trying to create a measure that compares the issue severity for the **current milestone** against the **previous milestone** for each project. The measure should return:
- **"Reduced"** if the issue severity has decreased,
- **"Increased"** if the issue severity has increased,
- **"No Change"** if the issue severity is the same, or
- **"No Data"** if there is no previous milestone available for comparison.

Once I’ve created this, I also want to count how many distinct **Project IDs** have had their issue severity reduced, and display that number in a **Card Visual**.

---

### Measures I've Created So Far:

#### 1. **Previous Milestone Measure**:
This measure calculates the date of the previous milestone for the same **Project ID**.

```DAX
Previous Milestone Measure =
VAR CurrentMilestone = SELECTEDVALUE('ProjectTable'[Project_Milestone_Date])
VAR ProjectID = SELECTEDVALUE('ProjectTable'[ProjectID_PM])

RETURN
   CALCULATE(
       MAX('ProjectTable'[Project_Milestone_Date]),
       'ProjectTable'[ProjectID_PM] = ProjectID &&
       'ProjectTable'[Project_Milestone_Date] < CurrentMilestone
   )
```

#### 2. **Previous Severity Measure**:
This measure retrieves the issue severity for the **previous milestone**.

```DAX
Previous Severity Measure =
VAR CurrentMilestone = SELECTEDVALUE('ProjectTable'[Project_Milestone_Date])
VAR ProjectID = SELECTEDVALUE('ProjectTable'[ProjectID_PM])
VAR PreviousMilestone = [Previous Milestone Measure]

RETURN
   CALCULATE(
       FIRSTNONBLANK('ProjectTable'[Issue_Severity_PM], 1),
       'ProjectTable'[ProjectID_PM] = ProjectID &&
       'ProjectTable'[Project_Milestone_Date] = PreviousMilestone
   )
```

#### 3. **Current Period Measure**:
This measure simply returns the current selected milestone date.

```DAX
Current Period Measure =
SELECTEDVALUE('ProjectTable'[Project_Milestone_Date])
```

#### 4. **Current Severity Measure**:
This measure retrieves the issue severity for the **current milestone**.

```DAX
Current Severity Measure =
VAR CurrentMilestone = SELECTEDVALUE('ProjectTable'[Project_Milestone_Date])
VAR ProjectID = SELECTEDVALUE('ProjectTable'[ProjectID_PM])

RETURN
   CALCULATE(
       SELECTEDVALUE('ProjectTable'[Issue_Severity_PM]),
       'ProjectTable'[ProjectID_PM] = ProjectID &&
       'ProjectTable'[Project_Milestone_Date] = CurrentMilestone
   )
```

#### 5. **Issue Severity Change Measure**:
This measure compares the issue severity between the current and previous milestones, and returns "Reduced", "Increased", or "No Change".

```DAX
Issue Severity Change Measure =
VAR CurrentSeverity = [Current Severity Measure]
VAR PreviousSeverity = [Previous Severity Measure]

-- Convert severity levels to numerical values
VAR SeverityLevel = SWITCH(
   CurrentSeverity,
   "High", 5,
   "Medium High", 4,
   "Medium", 3,
   "Medium Low", 2,
   "Low", 1,
   BLANK()
)

VAR PreviousSeverityLevel = SWITCH(
   PreviousSeverity,
   "High", 5,
   "Medium High", 4,
   "Medium", 3,
   "Medium Low", 2,
   "Low", 1,
   BLANK()
)

RETURN
   IF(
       ISBLANK(CurrentSeverity) || ISBLANK(PreviousSeverity),
       "No Data",
       IF(
           SeverityLevel > PreviousSeverityLevel,
           "Increased",
           IF(
               SeverityLevel < PreviousSeverityLevel,
               "Reduced",
               "No Change"
           )
       )
   )
```

#### 6. **Is Reduced Measure** (Helper Measure):
This measure returns **1** if the issue severity is "Reduced", and **0** otherwise.

```DAX
Is Reduced Measure =
IF(
   [Issue Severity Change Measure] = "Reduced",
   1,
   0
)
```

---

### Problem/Issue:

I have successfully created measure called **"Is Reduced Measure"** and verified that it works properly when added to a table visual. It correctly identifies when the issue severity has been reduced.

Next, I wanted to count the distinct **Project IDs** where the severity has reduced. To do that, I created the following measure:

#### 7. **Distinct Reduced Project Count**:

```DAX
Distinct Reduced Project Count =
CALCULATE(
   DISTINCTCOUNT('ProjectTable'[ProjectID_PM]),
   FILTER(
       'ProjectTable',
       [Is Reduced Measure] = 1
   )
)
```

I can create this measure successfully without any errors. However, when I try to use it in a **Card Visual**, I see no result—it just shows a blank value.

### Question:

Why am I getting a blank result when using the **Distinct Reduced Project Count** measure in a card visual? What might I be missing or doing wrong? How can I count the number of distinct **Project IDs** where the issue severity has reduced and display that in a card? I will be very thankful. Thanks in advance.

---

### Summary of Measures:

- **Previous Milestone Measure**: Finds the previous milestone for a project. (basically retrieves previous date)
- **Previous Severity Measure**: Retrieves the issue severity for the previous milestone.
- **Current Period Measure**: Returns the current milestone date. (basically retrieves selected / current date)
- **Current Severity Measure**: Retrieves the issue severity for the current milestone.
- **Issue Severity Change Measure**: Compares the current and previous severities and returns "Reduced", "Increased", or "No Change".
- **Is Reduced Measure**: A helper measure returning **1** if the issue severity has reduced.
- **Distinct Reduced Project Count**: Counts distinct **Project IDs** where the severity has been reduced.

[Anonymous · Accepted solution]

Hi @thinker_02 

Agreed with AllisonKennedy, this problem is related to row contexts. Your measures [Previous Milestone Measure] and [Previous Severity Measure] are not returning the correct results. Therefore the final card visual returns blank.

Correct your measures with the following DAX:

Previous Milestone Measure = 
VAR CurrentMilestone = SELECTEDVALUE('ProjectTable'[Project_Milestone_Date])
VAR ProjectID = SELECTEDVALUE('ProjectTable'[ProjectID_PM])
RETURN
    CALCULATE(
        MAX('ProjectTable'[Project_Milestone_Date]),
        FILTER(
            ALL('ProjectTable'),
            'ProjectTable'[ProjectID_PM] = ProjectID &&
            'ProjectTable'[Project_Milestone_Date] < CurrentMilestone
        )
    )

Previous Severity Measure = 
VAR PreviousMilestone = [Previous Milestone Measure]
VAR ProjectID = SELECTEDVALUE('ProjectTable'[ProjectID_PM])
RETURN
    CALCULATE(
        FIRSTNONBLANK('ProjectTable'[Issue_Severity_PM], 1),
        FILTER(
            ALL('ProjectTable'),
            'ProjectTable'[ProjectID_PM] = ProjectID &&
            'ProjectTable'[Project_Milestone_Date] = PreviousMilestone
        )
    )

The rest of the measures remain unchanged.

Here is my test result, details refer to uploaded pbix file.

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

[AllisonKennedy]

@thinker_02  Are you still trying to solve this? I'm not sure if the solution provided does the distinct count you're looking for, if it's solved in a different way can you let us know? If you're still trying to figure it out, please @ mention me and I'll provide additional details if I can.

Cheers!

[AllisonKennedy]

@thinker_02 

Great post - thanks for all the background info. It helps.

I believe the issue here is related to evaluation context. When you count the projects within the scope of a single project, all your measures work. However, in a card visual you have more than one project in scope, and therefore most of your measures will provide Blank for the SELECTEDVALUE( projectID )

To fix this, you need to wrap your final measure inside a row context for ProjectID. You could use any of the iterators. 

Do you have a dimProject table where each ProjectID appears only once? If so, you can use:

COUNTROWS(
 FILTER( dimProject, [Is Reduced Measure] = 1 )
)

Thing to be aware of - some projects may have reduced and increased over the date range, so if your card visual has no date slicers, you may get a higher number than you're expecting. If you only want to count the latest, you'll need to update your measures accordingly to add in that context. 

If you don't have dimProject table yet, I suggest you get one, it will make this challenge and many others much easier: Excel with Allison: It's Complicated: Relationships in Power BI Part 1 (Fact vs Dimension)