show items with no data not available if using a measure

[emma313823]

All,

I have a measure I'm using as a means of dynamically always using the current year detail. There are some cells in my table that are blank and I want to force a value even if it is zero dollars ($0.00).  I'm seeing that if you select the visual, then values you should see 'show items with no data', but it is not available. 

I'm using a matrix table visual. Can anyone offer any asstance on how to force the visual to show $0.00?

CurrYrComm =

CALCULATE(SUM(tblIncomingCommissions[Check_Value]),

FILTER('Calendar_Dates',

Year('Calendar_Dates'[Date])=YEAR(Today())))

[SamWiseOwl · Accepted solution]

Hi @emma313823 

Show items with no data only works if the Row exists and has no values.

I.e The row Company = A, Month = Jan, Sales = Blank()

If the row itself doesn't exist then Show items with no data won't work.

There are two ways to do it:

1. In the query editor add the missing rows
2. Create a table of Months and join that to the month column and then use this in the visual

Hope this helps!

[Ashish_Mathur]

Hi,

In the Calendar Table, create a column of Year.  Drag Year from the Calendar Table into a slicer and select 2024.  Simplify your measure to

CurrYrComm = COALESCE(SUM(tblIncomingCommissions[Check_Value]),0)

Hope this helps.

[Anonymous · Accepted solution]

Hi All,

Firstly,  @SamWiseOwl thank you for your solution!

And @emma313823 according to my understanding,  I want to share another method.

You can add a COALCSCE function to your DAX function to potentially help you out, hope this helps.

CurrYrComm = COALESCE(
CALCULATE(SUM(example_commissions[Check_Value]),
FILTER('Calendar_Date',
Year('Calendar_Date'[Date])=YEAR(Today())
)
),0)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl · Accepted solution]

Hi @emma313823 

Show items with no data only works if the Row exists and has no values.

I.e The row Company = A, Month = Jan, Sales = Blank()

If the row itself doesn't exist then Show items with no data won't work.

There are two ways to do it:

1. In the query editor add the missing rows
2. Create a table of Months and join that to the month column and then use this in the visual

Hope this helps!