Help with DAX Filters please

Question

Hello everyone, I'd be very greatful if someone could help me out!I am trying to compute new customers&nbsp;[newcustomercount]&nbsp;and have used the dax code used here for this:https://www.sqlbi.com/articles/computing-new-customers-in-dax/I have also created a copy of my&nbsp;'Date'&nbsp;table called&nbsp;'PreviousDate'&nbsp; to be able to select a single month in a visual and get 6 months filter the previous 6 months of the date table. Code for this has come from here:https://www.youtube.com/watch?v=d8Rm7dwM6gc&amp;t=601sHere is a link to my pbix file in case you'd like to check it out! Thanks if you do!https://drive.google.com/drive/folders/1j93uX4dUL6JoTJxDt3LMPWdHQD0_Nssj?usp=sharingThe ProblemI have noticed that I get a different result when,calling a measure within calculate:[prev6mths|callingmeasure].........VAR Result =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;[newcustomercount],&nbsp; &nbsp; &nbsp; &nbsp; REMOVEFILTERS ('Date'),............vs.&nbsp;writing the measure within calculate:[prev6mths|writtingmeasure]................VAR Result =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ...............Results:&nbsp;&nbsp;Relationships:&nbsp;&nbsp;My Measures:newcustomercount&nbsp;=COUNTROWS&nbsp;(&nbsp; &nbsp;&nbsp;FILTER&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATETABLE&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;VALUES&nbsp;(&nbsp;NewRevenue[Account_Id]&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"DateOfFirstBuy",&nbsp;CALCULATE&nbsp;(&nbsp;MIN&nbsp;(&nbsp;NewRevenue[Report_Month]&nbsp;) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ALL&nbsp;(&nbsp;'Date'&nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CONTAINS&nbsp;(&nbsp;VALUES&nbsp;(&nbsp;'Date'[Date]&nbsp;),&nbsp;'Date'[Date],&nbsp;[DateOfFirstBuy]&nbsp;)&nbsp; &nbsp; ))prev6mths|callingmeasure&nbsp;=VAR&nbsp;NumOfMonths = -6VAR&nbsp;ReferenceDate =&nbsp; &nbsp;&nbsp;MAX&nbsp;( 'Date'[Date] )VAR&nbsp;PreviousDates =&nbsp; &nbsp;&nbsp;DATESINPERIOD&nbsp;( 'PreviousDate'[Date], ReferenceDate, NumOfMonths, MONTH )VAR&nbsp;Result =&nbsp; &nbsp;&nbsp;CALCULATE&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;[newcustomercount],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;REMOVEFILTERS&nbsp;( 'Date' ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;KEEPFILTERS&nbsp;( PreviousDates ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;USERELATIONSHIP&nbsp;( 'Date'[Date], 'PreviousDate'[Date] )&nbsp; &nbsp; )RETURN&nbsp; &nbsp; Resultprev6mths|writtingmeasure&nbsp;=VAR&nbsp;NumOfMonths&nbsp;= -6VAR&nbsp;ReferenceDate&nbsp;=&nbsp; &nbsp;&nbsp;MAX&nbsp;(&nbsp;'Date'[Date]&nbsp;)VAR&nbsp;PreviousDates&nbsp;=&nbsp; &nbsp;&nbsp;DATESINPERIOD&nbsp;(&nbsp;'PreviousDate'[Date],&nbsp;ReferenceDate,&nbsp;NumOfMonths,&nbsp;MONTH&nbsp;)VAR&nbsp;Result&nbsp;=&nbsp; &nbsp;&nbsp;CALCULATE&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FILTER&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CALCULATETABLE&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;VALUES&nbsp;(&nbsp;NewRevenue[Account_Id]&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;"DateOfFirstBuy",&nbsp;CALCULATE&nbsp;(&nbsp;MIN&nbsp;(&nbsp;NewRevenue[Report_Month]&nbsp;) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ALL&nbsp;(&nbsp;'Date'&nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CONTAINS&nbsp;(&nbsp;VALUES&nbsp;(&nbsp;'Date'[Date]&nbsp;),&nbsp;'Date'[Date],&nbsp;[DateOfFirstBuy]&nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;REMOVEFILTERS&nbsp;(&nbsp;'Date'&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;KEEPFILTERS&nbsp;(&nbsp;PreviousDates&nbsp;),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;USERELATIONSHIP&nbsp;(&nbsp;'Date'[Date],&nbsp;'PreviousDate'[Date]&nbsp;)&nbsp; &nbsp; )RETURN&nbsp; &nbsp;&nbsp;Result&nbsp;Thanks for reading, I would be very greatful if anyone could help me out!&nbsp;amitchandak&nbsp;Idrissshatila&nbsp;&nbsp;

greg_deckler · Answer

Lhans&nbsp;One of the many reasons to avoid CALCULATE IMHO. Plus you are using TI functions and that's not idea. CALCULATE and the TI functions are pretty much black boxes that you can't really troubleshoot.
&nbsp;
It's really not an uncommon problem. See this video link which shows the same kind of problem even with a simple SUM measure. The link should be to the exact time in the video where I discuss this. If it doesn't work, skip to 26:13.

lhans · Answer

Ritaf1983&nbsp;parry2k&nbsp;Ahmedx&nbsp;Ashish_Mathur&nbsp;lbendlin&nbsp;kpost&nbsp;jdbuchanan71&nbsp;Greg_Deckler&nbsp;MFelix&nbsp; Really appreciate it if any of you guys have an idea why there is a difference calling a measure provides the wrong result 🙂

lhans · Answer

How frustrating, that no one knows how the calculate function will perform in certain scenarios. I guess seeing as there are no other replies to this everyone is in agreeement with you Greg. Which is scary for me as you guys know more than just about anyone else about this language. The idea that sometimes calculate works and sometimes it doesn't and we can't clearly identify when these failures occur is terrible. I think this one flaw should be enough to drive anyone away from using DAX (with it's popular calculate methodology). I was really hoping that I was missing something sublte which would explain why some filters are or aren't being propagated through the code I wrote.
Thanks Greg_Deckler I watched your video.

Forum Discussion

Help with DAX Filters please

3 Replies

Related content

Help Please

Matrix Filtering is Confusing please help

Filter

Service Error - Need Help Please!

Please someone clarify this question