New Measure Thrashes Table but Measure Proven to Work

[christopher_cla]

Hi,

I've spent all afternoon on this and I can't figure out what's wrong.  I created a new measure based on some other measures in my table:

Census Tier = 
SWITCH( 
    TRUE(),
    [3 Month Avg HH/HO] >= 0 && [3 Month Avg HH/HO] <= 150, "0-150",
    [3 Month Avg HH/HO] >= 150 && [3 Month Avg HH/HO] <= 500, "151-500",
    [3 Month Avg HH/HO] > 500, "500+",
    "Unknown" // This is the ELSE condition, capturing all other cases
)

But every time I pull it into the table with all the other measures (including the measures referenced in the new measure), the table blanks all of the fields in the same table and only the columns from other tables remain. I thought this was a relationship issue but that usually breaks the table in different ways and I don't know what relationship would apply if all I'm doing is assessing a value in a row and expecting to put a different value in the next column.

I have demonstrating that the new measure DOES work.  I did this by putting the new measure in a card and when I select a row from my table, the measure in my new card displays correctly.

I thought maybe PBI didn't like me doing a measure of a measure but we have that already elsewhere in the model and it displays fine: 

3 Month Avg HH/HO = 'fact CompanyMetricsByLOBMonth'[3 Month Avg HH]+'fact CompanyMetricsByLOBMonth'[3 Month Avg HO]

Can somebody help me understand why I can't pull the measure at the top of this post into the same table as the rest of the data without it making all my other measures disappear?

[christopher_cla]

that worked perfectly! thank you so much.  I'm ashamed I wasted so much time on this Friday.

[AshokKunwar]

@christopher_cla 

Your welcome,

If have any other question free to ask me 

[AshokKunwar]

HII @christopher_cla 

If this "Filter Guard" logic stops your table from blanking out, please mark this as the "Accepted Solution"!

[AshokKunwar · Accepted solution]

@christopher_cla 

To Fix It  , please try this formula 👇

Census Tier = 
VAR _AvgValue = [3 Month Avg HH/HO]
RETURN
IF(
    ISBLANK(_AvgValue), 
    BLANK(),
    SWITCH( 
        TRUE(),
        _AvgValue >= 0 && _AvgValue <= 150, "0-150",
        _AvgValue > 150 && _AvgValue <= 500, "151-500",
        _AvgValue > 500, "500+",
        "Unknown"
    )
)

If this "Filter Guard" logic stops your table from blanking out, please mark this as the "Accepted Solution"!

[cengizhanarslan]

Please try the formula below:

Census Tier =
VAR v = [3 Month Avg HH/HO]
RETURN
IF (
    ISBLANK ( v ),
    BLANK (),          -- or "Unknown" if you truly want to display it
    SWITCH (
        TRUE (),
        v >= 0 && v <= 150, "0-150",
        v > 150 && v <= 500, "151-500",
        v > 500, "500+"
    )
)

[AshokKunwar]

Hii @christopher_cla 

The reason your measure works in a Card is that a Card only displays a single aggregate value based on your slicer selection. However, when you put it in a Table, Power BI tries to calculate that measure for every row of the table. If there is a "blind spot" in the relationships between your dimension table (the rows in your table) and the fact CompanyMetricsByLOBMonth table, the engine fails to calculate the result per row and blanks out the visual to avoid showing incorrect data.

​The Solution: Fixing the Evaluation Context

​Step 1: Check for Cross-Filter Direction

​Ensure the relationship between your Dimension table (e.g., Company) and your Fact table is set correctly.

1. ​Go to the Model View.
2. ​Locate the relationship between the table providing the rows and fact CompanyMetricsByLOBMonth.
3. ​Ensure the Cross-filter direction is set to Single (Dimensions filter Fact) or Both if necessary.

​Step 2: Add a Filter Guard (The "IsFiltered" Pattern)

​If your 3 Month Avg HH/HO measure is complex, it might be returning BLANK for certain rows, which then causes the SWITCH function to behave unexpectedly. Update your Census Tier measure to explicitly check if data exists for that row:

Census Tier = 
VAR _AvgValue = [3 Month Avg HH/HO]
RETURN
IF(
    NOT ISBLANK(_AvgValue),
    SWITCH( 
        TRUE(),
        _AvgValue >= 0 && _AvgValue <= 150, "0-150",
        _AvgValue > 150 && _AvgValue <= 500, "151-500",
        _AvgValue > 500, "500+",
        "Unknown"
    ),
    BLANK() // Do not show anything if the base measure is blank
)

Step 3: Check for "Auto-Exist" Issues

​If you are pulling columns from multiple tables into one visual, Power BI uses a behavior called Auto-Exist. If there are no data combinations between those tables, the rows disappear.

• ​Test: Remove columns from the table one by one. If the data reappears when you remove a specific column, you have found the table with the missing relationship.

If this "Filter Guard" logic stops your table from blanking out, please mark this as the "Accepted Solution"!

[OwenAuger]

Hi @christopher_cla 

One possible issue with the measure is that it returns a nonblank result even when [3 Month Avg HH/HO] is blank. This could be the underlying cause of unexpected results in the Table visual.

To fix this, I suggest adding a NOT ISBLANK check and return BLANK when [3 Month Avg HH/HO] is blank. You should also store the value of [3 Month Avg HH/HO] in a variable for performance/readability.

Without changing any other logic, here is what I would suggest:

Census Tier =
VAR MeasureVal = [3 Month Avg HH/HO]
RETURN
    IF (
        NOT ISBLANK ( MeasureVal ),
        SWITCH (
            TRUE (),
            MeasureVal >= 0 && MeasureVal <= 150, "0-150",
            MeasureVal >= 150 && MeasureVal <= 500, "151-500",
            MeasureVal > 500, "500+",
            "Unknown" // This is the ELSE condition, capturing all other cases
        )
    )

Does this work as intended?