cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````// 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 14
Anonymous
Not applicable

``````// 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 )``````

Anonymous
Not applicable
``````// 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 )
)
)``````
Anonymous
Not applicable

Thank you all for the help.

@Anonymouswins this one, thank you so much!

Super User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

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.

Super User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

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 User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

@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

Super User

@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?

_____________________________________
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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

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?

Super User

@Anonymous , I think you have work like that.

see if segmentation can help

Super User

@Anonymous

Hope the last two are calculated Columns?
What is formula for Visit Bucket?

________________________

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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

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?

@Fowmy

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.