MTD calculation with blank dimension when using if blank then 0

[joechew123]

My mapping there is no isuse but when i use dax below to deflaut blank as zero 

MTD Attrition = if(isblank(TOTALMTD(COUNT(Attrition[Date]),'DAX DateTable'[Date])),0,TOTALMTD(COUNT(Attrition[Date]),'DAX DateTable'[Date]))

There will issue with master cost centre mapping with blank.

When i use 

MTD Attrition = TOTALMTD(COUNT(Attrition[Date]),'DAX DateTable'[Date]) is normal. 

My ojective is when my slicer filter the specifc country with blank with default to 0 without the blank dimenion. 

[FarhanJeelani · Accepted solution]

Hi @joechew123 

The issue you're experiencing is due to how Power BI handles blank dimensions when using IF(ISBLANK(...), 0, ...) in your DAX measure. The condition is triggering behavior where blank dimensions create conflicts with your data mappings, especially with master cost center mapping.

Here’s how you can address your requirement to default blanks to 0 while ensuring the blank dimension doesn't disrupt your mapping:

Revised DAX Measure:

Instead of directly wrapping TOTALMTD with an IF(ISBLANK(...)), use a combination of CALCULATE, ISBLANK, and SUMMARIZE to handle blank dimensions gracefully.

MTD Attrition = 
VAR MTDValue = TOTALMTD(COUNT(Attrition[Date]), 'DAX DateTable'[Date])
RETURN 
    IF(
        ISBLANK(MTDValue), 
        0, 
        MTDValue
    )

Adjusting for Blank Dimension:

If the blank dimensions are causing issues, you can filter them out at the measure level while still defaulting to 0 for blanks. Use KEEPFILTERS to maintain slicer filters but exclude blank values explicitly:

MTD Attrition = 
VAR MTDValue = 
    CALCULATE(
        TOTALMTD(COUNT(Attrition[Date]), 'DAX DateTable'[Date]),
        'Your Dimension Table'[Column] <> BLANK() -- Adjust with the specific column causing blanks
    )
RETURN 
    IF(
        ISBLANK(MTDValue), 
        0, 
        MTDValue
    )

Key Points:

1. Exclude Blanks Explicitly: The BLANK() filter ensures blank dimensions are ignored during calculations.
2. Handle Zero Defaults at Output: The IF(ISBLANK(...), 0, ...) ensures that any final output remains 0 instead of blank.
3. Debug Filters with Visuals: Add a table visual to check how the slicer interacts with your dimensions and whether blanks exist due to unrelated relationships or missing mappings.

Additional Tips:

• If you're dealing with a master cost center mapping table, ensure that your relationship is properly set (e.g., one-to-many) and not introducing unintentional blanks.
• Test intermediate results using a simple measure like COUNTROWS or VALUES to debug where blanks occur.

Let me know if this helps resolve your issue!

Please accept this as solution if it helps. Appreciate Kudos.