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 )
Super UserAnonymous
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 🙂
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.
- Fowmy
Anonymous
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 🙂