Dynamic OVER(PARTITION BY) DAX Equivalent

[Anonymous]

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!

[Anonymous · Accepted solution]

// 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]

// 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]

Thank you all for the help.

@Anonymouswins this one, thank you so much!

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

YouTube, LinkedIn

[Anonymous]

@Fowmy 

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 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy 

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 🙂

YouTube, LinkedIn

[Anonymous]

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

YouTube, LinkedIn

[Anonymous]

@Fowmy 

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?

[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 

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 🙂

YouTube, LinkedIn

[Anonymous]

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