topic Re: Issue with Displaying All Days as Values from Calendar Table Using DAX Measure in DAX Commands and Tips

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

Madhu155 — Thu, 28 Nov 2024 11:13:20 GMT

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.

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

FarhanJeelani — Thu, 28 Nov 2024 11:23:38 GMT

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:

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).
Check Relationships: Confirm the relationship between your calendar table and fact table is active and correctly configured.
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.

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

FreemanZ — Thu, 28 Nov 2024 14:34:23 GMT

hi Madhu155 ,

try like:

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

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

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

Madhu155 — Fri, 29 Nov 2024 06:07:42 GMT

Thank you

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

Madhu155 — Fri, 29 Nov 2024 06:12:55 GMT

Thank you

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

Madhu155 — Fri, 29 Nov 2024 06:41:45 GMT

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]))
)

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

Rupak_bi — Fri, 29 Nov 2024 08:31:00 GMT

Hi Madhu155 ,

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

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

Anonymous — Mon, 02 Dec 2024 02:22:58 GMT

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.