基于DAX公式的Excel数据透视图显示空列问题求助

Hey Paul, let's tackle this issue you're having with getting all four priority values (Critical, High, Medium, Low) to show up consistently in your pivot table—even when there are no failed test cases for a priority.

The core problem here is that your current pivot table uses the Execution Priority column directly from your Test_Schedule table. When a priority has no failed cases, it gets filtered out of the pivot's context—even though you added +0 to your measure to show 0 instead of blank. To fix this, we need an independent dimension table that always holds all four priority values, regardless of your test data.

Here's how to resolve it step by step:

1. Create a Standalone Priority Dimension Table #

In Power Pivot, build a new table with all four priority values using this DAX formula:

Priority_Dim = DATATABLE(
    "Execution Priority", STRING,
    {
        {"Critical"},
        {"High"},
        {"Medium"},
        {"Low"}
    }
)

This table will act as your reliable, unchanging source for all priority values, no matter what's happening in your test schedule data.

2. Link the Dimension Table to Your Test Schedule #

Head to the Relationships view in Power Pivot and create a one-to-many relationship between Priority_Dim[Execution Priority] and Test_Schedule[Execution Priority] (one priority in the dimension table links to many test cases in the schedule).

3. Update Your Measures to Use the Dimension Table #

Modify your Cases Remaining measure to reference the dimension table, ensuring it checks every priority even if there are no failed cases for it:

Cases Remaining = 
VAR SelectedPriority = SELECTEDVALUE(Priority_Dim[Execution Priority])
RETURN
COUNTROWS(FILTER(Test_Schedule, Test_Schedule[Result] <> "Pass" && Test_Schedule[Execution Priority] = SelectedPriority)) + 0

For your Remaining Percent Less Passed measure, adjust it to calculate across all priorities (including those with 0 failed cases):

Remaining Percent Less Passed = 
VAR TotalRemainingCases = SUMX(Priority_Dim, [Cases Remaining])
RETURN
DIVIDE([Cases Remaining], TotalRemainingCases, 0)

4. Refresh Your Pivot Table #

In your dashboard, replace the Execution Priority field from Test_Schedule with the one from your new Priority_Dim table.

Now you’ll see all four priority values listed consistently, with 0 showing up for any priority that has no failed test cases, and your percentage calculation will work correctly across every row.

内容的提问来源于stack exchange，提问作者Paul Coles