Issue with Displaying All Days as Values from Calendar Table Using DAX Measure

[Madhu155]

Hi Everyone,

I am facing an issue with the "Day" column from the calendar table. When using the following measure, only the day values are displayed from the column which as values from the fact table.

However, I want all-day values to be shown from the column and I want the formula to show "zero" or leave the cell blank if there is no value for that day.

Below is the measure I am using:

DAX Formula:

 

=DISTINCTCOUNT('_Orgination - Final_'[LoanId])

Note 1: There is a many-to-one relationship between the fact table and the calendar table, where the "many" side is the fact table and the "one" side is the calendar table.

Note 2: The "Days[Row Label]" is from the calendar table.

Note 3: I have tried several approaches but have not been able to achieve the desired result.

[FarhanJeelani]

Hi @Madhu155,

To display all days from the calendar table and show a 0 (or blank) for days without data, you need to modify your measure to use a function that respects the full context of the calendar table. Here’s an updated DAX formula you can try:

Measure = 
CALCULATE(
    DISTINCTCOUNT('_Orgination - Final_'[LoanId]),
    CROSSFILTER('Calendar'[Date], '_Orgination - Final_'[Date], BOTH)
)
+ 0

If you want to display 0 explicitly or leave the cells blank, adjust the measure:

For 0:

Measure = 
IF(
    ISBLANK(CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))),
    0,
    CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))
)

For blank cells:

Measure = 
IF(
    ISBLANK(CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))),
    BLANK(),
    CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))
)

Key Steps:

1. Ensure Your Calendar Table Has Full Dates: Verify that your calendar table includes all required days (e.g., a full range from January 1 to December 31).
2. Check Relationships: Confirm the relationship between your calendar table and fact table is active and correctly configured.
3. Use a Visual That Supports Missing Data: Use visuals like a table or matrix and ensure the "Show items with no data" option is enabled.

Let me know if this works for your scenario!

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

[FreemanZ]

hi @Madhu155 ,

try like:

measure = =DISTINCTCOUNT('_Orgination - Final_'[LoanId]) + 0

or feed the pivot table row with the day column from the calendar table.

[Madhu155]

Thank you 

[Madhu155]

Thank you 

[Madhu155]

@FarhanJeelani i was able to get 0 but I wanted blank, the formula below is not working, still it showing in aggregated format.  

Measure = 
IF(
    ISBLANK(CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))),
    BLANK(),
    CALCULATE(DISTINCTCOUNT('_Orgination - Final_'[LoanId]))
)

[Rupak_bi]

Hi @Madhu155 ,

Just add Zero in your measure , it will work
=DISTINCTCOUNT('_Orgination - Final_'[LoanId])+0

[Anonymous · Accepted solution]

Thanks for the replies from Rupak_bi, FreemanZ and FarhanJeelani.

Hi @Madhu155 ,

If you want to display a value that has no data and does not need to be displayed as 0. You can select a visual. In the Values fields well, right-click the field and select Show items with no data from the menu.

The Show items with no data feature lets you include data rows and columns that don't contain measure data (blank measure values).

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.