Dax Help on showing 0 Count for Column Chart

[bdehning]

I am trying the following for Y Axis on a chart but not seeing any months of 0 count like I want.   How could I tweak this?

Test Count of Total Gross Incurred +0 =

  VAR __Max = MAX('CalendarTable'[Dates])

  VAR __Count = COUNT(LossRun[Total Gross Incurred]) + 0

  VAR __RT = COUNTROWS(FILTER(ALL('LossRun'), [InceptionYear] <= __Max))

  VAR __Result = IF(__RT = 0, BLANK(), __Count)

RETURN

  __Result

[bdehning]

Of course if  I use  VAR __Result = __Count, I am back to my original issue of having 0's for months outside the time they are with the company.   

[Ritaf1983]

Hi @bdehning 
There is a link for a more efficient method to handle the blanks/0 issues.

https://www.youtube.com/watch?v=_cT9PB72fu8&t=1537s

if it doesn't help 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

[bdehning]

I followed the video and got fairly close except I want to get to this, down to Month level, which has been my challenge.  This Chart, if working, should start from August 2022 for this customer.     

[bdehning]

I am having a tough time restricting table or chart to the Year and Month a Customer has been with the company, but be able to show 60 months of data if they have been customer that long.   Customers can join any month of the year.   

[bdehning]

Here is another thought.  I use the following measure to set the number of months.   

Rolling Month =

VAR __MAX_DATE =

    MAX ( 'LossRun'[Loss Date] )

RETURN

    DATEDIFF ( 'LossRun'[Loss Date], __MAX_DATE, MONTH ) + 1

I use "is less than or equal to 60" currently.   The company above only has 2 years and why all the front loading zeros.   

I have date field of Inception Date when companies start.  Could that field be used in the measure as a VAR somehow with Min and Max to limit the number of months shown?  

[Ritaf1983]

Hi @bdehning 
Again without data, I am not sure that I understand you correctly.
If you want to filter out dates that are after the last date in the fact table,
You can create flag measure like:
flag = if (MAX('CalendarTable'[Dates])<=  MAX ( 'LossRun'[Loss Date] ) && (min('CalendarTable'[Dates])>= min ( 'LossRun'[Loss Date] ),1,0)
and use this flag as a filter on the graph

Hopefully, it can help...

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

[bdehning]

I want to try your measure but it says Operator or Expression () is  not supported in this contect?

[Ritaf1983]

Hi @bdehning 
As I mentioned please create a small pbix with an example and share the link.

[bdehning]

Did you get the pbiax link?

[Ritaf1983]

Hi @bdehning 
Yes, and I must admit that I didn't understand the model.
There are many to many relationships,
The fact table doesn't relate to the calendar...
Very strange measure.

Sorry, the issue is much deeper than the question that you asked.

[bdehning]

Here is the fixed file.  See if you can do a measure now to fix the extra Zero issue.   

https://www.dropbox.com/scl/fi/l0ygu5v7flqb990gs9ewt/Test-Get-Rid-of-Extra-Zero-Months.pbix?rlkey=8r...

[bdehning]

You mean Table 1 and Table 2?     Table 1 is like Homeowners Policies for companies.   Many Policies for same Homeowner.  Table 2 is the Claims each Homeowner submits each year.  I do not see a way of having unique vales in either table.      

[bdehning]

On the test file you have, I did change the relationships between Table 1 and Table 2 and Calendar table.  Just the delete the 2 many to many and take Year from Table 1 to Calendar Table Dates and Year from Table 2 to Calendar Dates and pages still work.  Can you do something to help after that?

[bdehning]

I will. I got the file cut down, but I need to figure a way to reduce number of rows in tables and create dummy records.    

[bdehning]

https://www.dropbox.com/scl/fi/4by3t64vb12boyw131vpg/Test-Get-Rid-of-Extra-Zero-Months.pbix?rlkey=5i...

Here is the link.   

Company C has Policy Inception Date of 7/1/17.    But on Year Month Page I need to show 0 month for totals and would like to have rolling 60 months only is data was available.  Company B and C both have over 60 months of data for example.   

[Ritaf1983]

Hi @bdehning 
If the graph is based on a date table.
try to modify 
  VAR __Result = IF(__RT = 0, BLANK(), __Count)
to just  __Count
if the result is blank()
the months are not shown..

another option is to use 
"show items without data" 

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

[bdehning]

I can not just make VAR __Result = IF(__RT = 0, BLANK(), __Count)  be __Count.  I get unexpected expression ojn bith RETURN and Result?

Show items with no data does nothing.  

[bdehning]

I got it. Use  

VAR __Result = __Count