Hello everyone, I'd be very greatful if someone could help me out!
I am trying to compute new customers [newcustomercount] 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 'Date' table called 'PreviousDate' 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&t=601s
Here 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=sharing
The Problem
I have noticed that I get a different result when,
calling a measure within calculate:
[prev6mths|callingmeasure]
.........
VAR Result =
CALCULATE(
[newcustomercount],
REMOVEFILTERS ('Date'),............
vs.
writing the measure within calculate:
[prev6mths|writtingmeasure]
................
VAR Result =
CALCULATE(
COUNTROWS (
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
...............
Results:
Relationships:
My Measures:
newcustomercount =
COUNTROWS (
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( NewRevenue[Account_Id] ),
"DateOfFirstBuy", CALCULATE ( MIN ( NewRevenue[Report_Month] ) )
),
ALL ( 'Date' )
),
CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] )
)
)
prev6mths|callingmeasure =
VAR NumOfMonths = -6
VAR ReferenceDate =
MAX ( 'Date'[Date] )
VAR PreviousDates =
DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, NumOfMonths, MONTH )
VAR Result =
CALCULATE (
[newcustomercount],
REMOVEFILTERS ( 'Date' ),
KEEPFILTERS ( PreviousDates ),
USERELATIONSHIP ( 'Date'[Date], 'PreviousDate'[Date] )
)
RETURN
Result
prev6mths|writtingmeasure =
VAR NumOfMonths = -6
VAR ReferenceDate =
MAX ( 'Date'[Date] )
VAR PreviousDates =
DATESINPERIOD ( 'PreviousDate'[Date], ReferenceDate, NumOfMonths, MONTH )
VAR Result =
CALCULATE (
COUNTROWS (
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( NewRevenue[Account_Id] ),
"DateOfFirstBuy", CALCULATE ( MIN ( NewRevenue[Report_Month] ) )
),
ALL ( 'Date' )
),
CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] )
)
),
REMOVEFILTERS ( 'Date' ),
KEEPFILTERS ( PreviousDates ),
USERELATIONSHIP ( 'Date'[Date], 'PreviousDate'[Date] )
)
RETURN
Result
Thanks for reading, I would be very greatful if anyone could help me out!
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.
@Lhans 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.
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.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
@Ritaf1983 @parry2k @Ahmedx @Ashish_Mathur @lbendlin @kpost @jdbuchanan71 @Greg_Deckler @MFelix Really appreciate it if any of you guys have an idea why there is a difference calling a measure provides the wrong result 🙂
