DAX filter formula summing correctly but not changing by month

[Anonymous]

Hi there

I am using a formula to count the total number of entries in my dataset when another column = "TRUE".

As soon as I apply the FILTER part of my formula the totals by month all just pop to the absolute total. 

Here is the formula I am using:

Measure = CALCULATE(COUNTX(Database,Database[Total Referrals]),
FILTER(ALL(Database),Database[Referred to IHAT?]="TRUE"))

The grand total referred to IHAT is 4874. When I add this to a matrix by month, every month = 4874. It even sticks in November and December when I have no figures for these months yet. 

How do I get this measure to work properly???

[v-piga-msft]

Hi @Anonymous,

Your issue should be caused by the All function. Please have a try with the formula from TomMartens and Ashish_Mathur.

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[Ashish_Mathur]

Hi,

Why are you using the ALL function?  Try this

=CALCULATE(COUNTA(Database[Total Referrals]),Database[Referred to IHAT?]="TRUE")

Hope this helps.

[Anonymous]

Hi both thanks for your replies.

I was using ALL because i'm still learning DAX and don't really know what all the filters do!

The problem is, the [Total referrals] is a measure, so it doesn't appear in my options when I try a COUNTA. 

I also can't use a countx and a filter at the same time - the formula breaks.

Basically I want to divide [Total Referrals] by [Referred to IHAT]? where [Referred to IHAT?] is TRUE. Because the [Referred to IHAT] is a TRUE/FALSE column. So I wouldn't want my calculation to divide by the FALSE.

I guess I could have a separate measure which counts refered to IHAT = TRUE and then use that in my formula. I was just trying to nest it to keep it tidy!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[TomMartens]

Hey,

the ALL function that you are using inside your formula also removes existing filter coming from the month axis or other implicit filters like slicers, column, and row headers.

Try this instead:

FILTER(ALL(Database[Referred to IHAT?]),Database[Referred to IHAT?]="TRUE")

Just use ALL on the column that you want to filter, instead of using just the table name in combination with ALL.

If this does not help or provide you with the idea to solve your problem, you may consider to prepare a pbix with some sample data, upload the file to onedrive or dropbox and share the file.

Regards,

Tom