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_id], [Brand], [Channel]))
As you can see this gives the correct figure for each distinct brand/channel and puts the customer into the 1-2 visit bucket. However, if on the report I select a slicer for all brands (Orange, Blue and Green), the customer will still fall into the 1-2 visit bucket as the calculated column doesn't update. Instead, they should fall into the next bucket as they have 3 visits across all brands.
If number of visits is instead calculated as: CALCULATE(SUM([Visits Number]), ALLEXCEPT([Customer_id]))
This then works when looking at all brands and channels but if only one brand or channel is selected the opposite problem then occurs to the scenario above.
Can anyone provide a solution? Thank you!
// 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 )
14 Replies
// 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 )// If you want to be able to sum over // buckets as well... then you'll need // this version of the above measure: [# Customers in Bucket (additive across buckets)] = SUMX( Buckets, CALCULATE( var __currentBucket = SELECTEDVALUE( Buckets[Bucket] ) var __output = SUMX( VALUES( T[Customer ID] ), 1 * ( [Visit Bucket] = __currentBucket ) ) return if( __output > 0, __output ) ) )
- Fowmy
Super User
Anonymous
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?
- amitchandak
Anonymous , I think you have work like that.
see if segmentation can help
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization - Fowmy
Anonymous
Try this measure:Visit Bucket = VAR VISITS = COUNT(DATA1[VISIT NUMBER]) RETURN SWITCH( TRUE(), VISITS <=2, "1-2", VISITS >2&&VISITS <=4, "3-4", VISITS >4, "5+" )________________________
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 🙂
This seems to give a single bucket for the entire table. I need provide a distinctcount of customer_id within each bucket that changes depending on report level context.
amitchandakThanks, i will take a look.
- Fowmy
Anonymous
I am not sure on what context you are applying this measure, if you can share your report screenshot or attach a sample PBIX then It will make things clear.________________________
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 🙂
