Creating Dynamic Segments using DAX

[Anonymous]

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-DA...

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! 

[beaoliv123-_]

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

[Seth_C_Bauer]

@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

[Anonymous]

Hi @Seth_C_Bauer, 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! 

[Seth_C_Bauer]

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

[Anonymous]

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

[Anonymous]

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

[GilbertQ]

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?

[Anonymous]

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

[GilbertQ]

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.

[Seth_C_Bauer]

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

[Anonymous]

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