Issue with Conditional Measure Removing Category from Matrix Visual

[cashcrane]

Hi everyone,

I'm facing an issue with a conditional measure in my Power BI matrix visual where a specific category ("Site (Cost / Acre)") gets removed when applying dynamic calculations. Here are the details:

Data Structure:

I have a table called Metrics with the following columns:

• Index
• Group1
• Group2
• Category
• Project Name (Projects A through F)
• Value
• BLS Value
• CCI Value
• CurrentValue
• Factor
• IsNumeric

Matrix Visual Setup:

• Rows: Group1, Group2, Category
• Columns: Project Name
• Values: Normalized_Values (measure described below)

Measures:

NormalizedValue_Measure:

DAX

NormalizedValue_Measure = 
IF(
    OR(
        SELECTEDVALUE(Metrics[Group1]) = "07. Project Cost (Original)", 
        SELECTEDVALUE(Metrics[Group1]) = "08. Total"
    ),
    SUMX(Metrics, Metrics[CombinedFactor] * Metrics[IsNumeric]),
    SUMX(Metrics, Metrics[IsNumeric])
)

• Sitework_Normalized:

  DAX

Sitework_Normalized = 
CALCULATE(
    [NormalizedValue_Measure],
    Metrics[Category] = "200 Sitework"
)

• Sitework_Acres:

  DAX

Sitework_Acres = 
CALCULATE(
    SUM(Metrics[IsNumeric]), 
    Metrics[Category] = "Site - Acres"
)

• Normalized_Values:

  DAX

Normalized_Values = 
IF(
    SELECTEDVALUE(Metrics[Category]) = "Site (Cost / Acre)",
    DIVIDE([Sitework_Normalized], [Sitework_Acres], 0),
    [NormalizedValue_Measure]
)

Issue:

When I place the Normalized_Values measure in the matrix, the row for "Site (Cost / Acre)" gets removed. This happens no matter how I adjust the conditional logic in the measure. I've tried variations using SWITCH, HASONEVALUE, ISFILTERED, and REMOVEFILTERS, but the category still disappears when it should be present.

I need the measure to:

• Calculate Sitework_Normalized / Sitework_Acres when the category is "Site (Cost / Acre)"
• Otherwise, return NormalizedValue_Measure
• Ensure that "Site (Cost / Acre)" remains visible in the matrix

Any guidance on how to resolve this issue would be greatly appreciated!

[pborah · Accepted solution]

Thank you @cashcrane , let's break it down now.

• You said that the Normalized_Values is what causing Site(Cost/Acre) to go blank. Do any of the conditions in that measure -
  • [Group1] = "07. Project Cost (Original), or
  • [Group1] = "08. Total" , have corresponding data for the column Site?
• Additionally, are the other measures that you calculated and are throwing into the matrix table, limiting the scope of data that the measure Normalized_Values works on?

Usually these issues arise from what you have in the data and not necessarily because of incorrect DAX. So I would start there. However that is not to say that once you figure out what your data is on a row by row level, your DAX code wont change.

[cashcrane]

Groups 07 and 08 do not contain data corresponding to Category = "Site (Cost / Acre)". The other measures do not limit the scope, they simply are used to create the factor in order to normalize the values based on selections in some slicers. 

However, I am going to accept this as a solution because I have now realized the new Cost / Acre can also be multiplied by the factor because it is the same as multiplying the cost by the factor and then dividing by acerage. Props to me for ignoring basic algebra and assuming a complex solution was needed. 
Thanks for attempting to help!

[pborah]

Thank you very much. Glad I was able to point things out.

[pborah]

As a very simple solution first, can you right click "Category" in your rows card and select "Show items with no data"?

[cashcrane]

I tried that and it does show the category now (see image)

However you can see via the original matrix that there are values there before changing the measure: