topic Re: Creating Dynamic Segments using DAX in Desktop

Creating Dynamic Segments using DAX

Anonymous — Wed, 10 May 2017 06:36:51 GMT

Hi everyone. I am trying to create a segmentation based on the members' latency and Average Ticket Value.

My segmentation looks something like this:

And I want to know how can I do it in DAX given the data set that I currently have.

*edited: With the current data set that I have, it looks something like this:

To get the ATV that will be used for segmentation, it is derived from the formula: Sum of Sales/Transaction Count

Transaction Count is the distinct count of the transaction ID in the data set.

As for the Latency which is also a factor in the segments, it is the average of the difference of the dates between the transaction of a member based on their card no. on the data set. I was able to get the Latency by the script provided to me previously from this topic: http://community.powerbi.com/t5/Desktop/Calculating-Date-Difference-between-2-or-more-dates-using-DAX/m-p/162465

And now since I got the Latency, I have to start creating the segments as seen above as the next step to my project.

Hoping for your response on this matter.

Thank you!

Re: Creating Dynamic Segments using DAX

Anonymous — Wed, 03 May 2017 03:08:32 GMT

Anonymous The easiest approach may be just creating a calculated column like:

Segment = IF(AND('Table1'[ATV] > 1500, 'Table1'[Latency] < 60), "SEGMENT A",
	IF(AND('Table1'[ATV] > 1500, 'Table1'[Latency]> 60), "SEGMENT B",
		IF(AND('Table1'[ATV] < 1500, 'Table1'[Latency] < 60), "SEGMENT C",
			IF(AND('Table1'[ATV] < 1500, 'Table1'[Latency] > 60), "SEGMENT D",""))))

or are you looking for something like this article by Marco Russo

Re: Creating Dynamic Segments using DAX

Anonymous — Wed, 10 May 2017 03:17:17 GMT

Hi Anonymous, I tried working on the script you gave me last time and I still got this message:

I believe my computer is powerful enough for Power BI since it runs on these:

Is it really a hardware issue? Or is there something that we need to do on the script? Hope you could help me on this. Thanks!

Re: Creating Dynamic Segments using DAX

Anonymous — Wed, 10 May 2017 14:00:24 GMT

Anonymous This looks like a hardware issue. Are you running 32 or 64-bit Power BI Desktop?

Here is a quick wiki that will help you determine at least on a high level some RAM issues. But this isn't typically an error that would be produced if the syntax was wrong.

Re: Creating Dynamic Segments using DAX

Anonymous — Thu, 11 May 2017 01:58:06 GMT

Hi Anonymous. I'm running on a 64 Bit Power BI Desktop and my pc has 12GB worth of RAM in it.

Re: Creating Dynamic Segments using DAX

GilbertQ — Thu, 11 May 2017 02:15:51 GMT

Hi Anonymous

If there is a memory error, you could potentially create the column in the Query Editor using the Custom Column? This should make the memory size smaller when loading the data into the model.

With that said I have also found when working with large text files and excel files that the memory consumption in Power BI Desktop is ever increasing. So to resolve this I close down all instances of Power BI Desktop. Then go into my Task Manager and where ever I find BPIDesktop.exe I right click and select End Task.

Then open the same file again and I am good to go. Might be a possible memory leak?

Re: Creating Dynamic Segments using DAX

Anonymous — Thu, 11 May 2017 02:16:53 GMT

Anonymous I also tried running my .pbix file in another desktop that runs on a 16GB and I also received the same error.

Re: Creating Dynamic Segments using DAX

Anonymous — Thu, 11 May 2017 02:36:01 GMT

GilbertQ do I just run the same script in the Custom Column?

Re: Creating Dynamic Segments using DAX

Anonymous — Thu, 11 May 2017 02:43:52 GMT

GilbertQ I don't think the custom column is going to work for me since my segmentation is mostly based on a measure or a calculated column. And also, I tried re-running the program after clearing up the Power BI.exe in my task manager.

Re: Creating Dynamic Segments using DAX

GilbertQ — Thu, 11 May 2017 03:13:01 GMT

Hi Anonymous

If that is the case you are indeed correct, and that you will have to create it with a measure. And possibly a disconnected table for your banding.

Re: Creating Dynamic Segments using DAX

Anonymous — Thu, 11 May 2017 13:33:56 GMT

Anonymous I agree with GilbertQ your dataset must be pretty large and would require a different approach. The link I shared in the first response contains the outline of how to create a seperate table to achieve the same results.

Re: Creating Dynamic Segments using DAX

beaoliv123-_ — Thu, 26 May 2022 14:36:26 GMT

Hi everyone, I wanted to do a very similar thing. However in my case I would like to classify my users into A and B. If a user visits my website 1 time a month, then it is classified as A, if the users visit more than 1 time a month, then it's B.

How do I create this rule dinamically?

Thanks