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 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.

 

 

 

"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.

7 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Madhu155 ,

     

    try like:

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

     

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

  • FarhanJeelani's avatar
    FarhanJeelani
    Super 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.

  • Madhu155's avatar
    Madhu155
    Frequent Visitor
    1 year ago

    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's avatar
      Rupak_bi
      Super User
      1 year ago

      Hi Madhu155 ,

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.