Forum Discussion
Dynamic OVER(PARTITION BY) DAX Equivalent
// 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 )
It is within a table.
| 1-2 Visits | 3-4 Visits | 5+ Visits |
| DistinctCount of Customer_ID | DistinctCount of Customer_ID | DistinctCount of Customer_ID |
Slicers: Brand, Channel, Date
I cannot share the file as it is against company policy to reveal any data, unfortunately. Hopefuly the above makes sense in relation to the original post. There are around 300,000 unique customer IDs in the table.
Super UserAnonymous
Create a disconnected table for the buckets
Create the following measure:
Visit Bucket =
VAR MINBKT = SELECTEDVALUE('Visit Bucket'[Min])
VAR MAXBKT = SELECTEDVALUE('Visit Bucket'[Max])
VAR VISIT = COUNT(DATA1[VISIT NUMBER])
RETURN
IF( VISIT >= MINBKT && VISIT <= MAXBKT , VISIT, BLANK() )
The results:
________________________
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 🙂
Fowmy This works perfectly when a single customer id is selected but when all are selected it displays blank, rather than a count within each bucket
- Fowmy
Anonymous
If you could share a set of sample data and the expected output then I will be able to help you out.
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
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 🙂
On the one you created, what result do you get if you select all brands, i.e. blue, green & orange; and select all channels.
Expected result should be 3 but for me it shows blank?