usage of Dax function filter

[Anonymous]

Hello,

I can't get my head around as to why the dax measure below (Number 1) doesn't work the way i want. I thought both 1 and 2 measures should behave the same way but not. Can someone explain me why the first dax measure doesn't filter to show only the last day records?

[Anonymous · Accepted solution]

Hi @Anonymous 

Please try my measure:

test3 = 
VAR _MaxDate = MAXX(ALL('Table'),'Table'[Gaming Date])
RETURN
CALCULATE(COUNT('Table'[Gaming Date]),FILTER('Table','Table'[Gaming Date]=_MaxDate))

My Sample:

Result is as below. Test1 and test2  are measures like yours, you see test3 will get the correct result dynamiclly instead of use "Date" fucntion.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @Anonymous 
That is exactly what I am after . you are a legend :).

Decided not to waste my time replying until I get a proper solution as I have given enough information. Really happy that it crossed you.

Thanks again.

Cheers,

Deevs

[parry2k]

@Anonymous just asked a simple question, Read this post to get your answer quickly. It helps everyone.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[parry2k]

@Anonymous what you want to achieve? You just asked why measure 1 is different than measure 2 but you never explained what you are looking for. @Anonymous gave an explanation of the difference between measure 1 and measure 2.

In nutshell, @Anonymous answered your original question but if you need a further solution, you should tell what you are looking for. 

Well done @Anonymous 

[Anonymous]

@parry2k 

Calm Down you might end up with high blood pressure lol.

@Anonymous 
I want to count the records but based on a specific time period, say based on the max day or min day of the table.. If I drag the date into a table along with the measure I should only get the values based on the time period applied within the dax measure. Hope that makes sense.

How do you modify the below measure to achivew that. I want the table to only show the last day, not all.

[Anonymous]

Hi @Anonymous 

I agree with @parry2k and really hope that you use his link on how to get answers quickly.

Your initial ask and the purpose of this post is fulfilled with my first reply.
To keep the sanity of the post you already have your answer. This is going to be a new post my friend.

You should appreciate people trying to help here investing our personal time we can be doing something else helping other get through with work task. which demands only one thing.

[Anonymous]

lol  "myfrind"   "Your initial ask and the purpose of this post is fulfilled with my first reply."

[Anonymous]

Hi @Anonymous 

To reply to your question about why measure1 doesn't work and measure2 works is 
Measure1 is still working on each row for max function and it sees the data in current row as max.
Now to make it work you need to let the whole calculation know that it needs to work on the entire selected table and that is what ALLSELECTED does.

[Anonymous]

@Anonymous 

I used that but got the same results.

[Anonymous]

Hi @Anonymous 

Remove filter funcion from your measure

[amitchandak]

@Anonymous , In the filter one,

You have to use as the example

calculate([measure], filter(allselected(Table), [Date] =max([Date]))

allselected is important

[Anonymous]

@amitchandak 

Still the same. not filtering the max day data.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.