Forum Discussion
Helper IIDAX: Views since last transaction
Hi all,
I have a table which contains: Date, ProductID, Views, Transactions.
Now I would like to see the number of views since the last transaction for each product. To calculate the last transaction date I use the following measure: [LastTransaction] = calculate(max(date);conversions>0;all(date))
For the views since last transaction I tried = calculate(sum(views);date>[LastTransaction]) but it gives me an error ("A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression."). I also tried using the Filter() command but I seemed to ignore the Date>LastTransaction statement.
I could use some help!
In this scenario, you can use FILTER() to filter dates later that you calcuated Last Transaction Date. The expression can be like:
ClicksSinceLastTransaction = CALCULATE(SUM(Sheet1[Views]),FILTER(ALL(Sheet1[Date]),Sheet1[Date]>Sheet1[LastTransaction]))
I also attached modifed .pbix file
4 Replies
- Greg_Deckler
Community Champion
Right, so if you expand out your formual, you essentially have:
calculate(sum(views);date>calculate(max(date);conversions>0;all(date))So, you are essentially using CALCULATE in a Filter condition which is not allowed.
You might try using MAXX instead of CALCULATE and see if that resolves the issue. And perhaps SUMX instead of CALCULATE. I'd have to play with it. Any chance you can post sample data?
joerykeizerNot sure if there is an easier way to post this but I've created & uploaded a small sample file for you to look at.
v-sihou-msftMicrosoft Employee
In this scenario, you can use FILTER() to filter dates later that you calcuated Last Transaction Date. The expression can be like:
ClicksSinceLastTransaction = CALCULATE(SUM(Sheet1[Views]),FILTER(ALL(Sheet1[Date]),Sheet1[Date]>Sheet1[LastTransaction]))
I also attached modifed .pbix file
