Forum Discussion
Dynamic OVER(PARTITION BY) DAX Equivalent
I have a table where I need to derive the number of visits per customer, depending on report level context. Number of Total Visits = CALCULATE(SUM([Visits Number]), ALLEXCEPT([Customer_...
// Total number of visits [# Visits] = SUM( T[Visits Number] ) // Returns the bucket for the // total number of visits; // you might need to adjust the // limits in the disconnected // Buckets table (500 might not // be enough). [Visit Bucket] = var __visitCount = [# Visits] return CALCULATE( SELECTEDVALUE( Buckets[Bucket] ), Buckets[Min] <= __visitCount, __visitCount <= Buckets[Max], ALL( Buckets ) ) // For any slicing, it shows you // the number of customers that fall // into a selected bucket. [# Customers in Bucket] = var __currentBucket = SELECTEDVALUE( Buckets[Bucket] ) var __output = SUMX( VALUES( T[Customer ID] ), 1 * ( [Visit Bucket] = __currentBucket ) ) return if( __output > 0, __output )
Fowmy
Super User
Super UserAnonymous
Hope the last two are calculated Columns?
What is formula for Visit Bucket?
You try a measure instead.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
Yes, the last two are calculated columns.
Visit Bucket = SWITCH(TRUE(), [Number of Total Visits]<=2, "1-2", [Number of Total Visits]>2&&[Number of Total Visits]<=4, "3-4", [Number of Total Visits]>4, "5+")
How would you write this into a measure and derive figures for all three buckets, or would it be three seperate measures?