Forum Discussion

Madhu155's avatar
Madhu155
Frequent Visitor
1 year ago
Solved

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

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...
"DAX "
"DAX " "DAX help" 'Need help'
CALENDER
DAX Command
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

FarhanJeelani's avatar
FarhanJeelani
Icon for Super User rankSuper User
1 year ago

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.