topic Re: Dynamic OVER(PARTITION BY) DAX Equivalent in DAX Commands and Tips

Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 08:32:50 GMT

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!

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Fowmy — Tue, 11 Aug 2020 08:48:36 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 08:57:45 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

amitchandak — Tue, 11 Aug 2020 09:10:59 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Fowmy — Tue, 11 Aug 2020 09:16:38 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 09:31:26 GMT

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.

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Fowmy — Tue, 11 Aug 2020 09:57:45 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 10:02:44 GMT

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.

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Fowmy — Tue, 11 Aug 2020 10:40:04 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 11:53:37 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Fowmy — Tue, 11 Aug 2020 13:00:33 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Tue, 11 Aug 2020 14:17:17 GMT

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?

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Wed, 12 Aug 2020 15:23:47 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Wed, 12 Aug 2020 15:28:49 GMT

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

Re: Dynamic OVER(PARTITION BY) DAX Equivalent

Anonymous — Wed, 12 Aug 2020 15:54:10 GMT

Thank you all for the help.

Anonymouswins this one, thank you so much!