Dynamic Segment Flow DAX measure

Question

Hey all,&nbsp;I've been struggling a lot today, trying to get something to work.What I'm trying to build is a dynamic segment migration report. For every week, every customer is put into one of 5 segments. (like "loyal" and "risky"). Because this is done for every week, you can show from which segment the user moved into which other segment. So imagine it is week 45, but I still want to see the segment migration between week 40 and 41, that should be possible. I want to be able to, with the help of slicers or a what-if parameter, to set the weeks I am going to compare to each other.&nbsp;The end result should show the following columns:- Program week of Source Segment- Source Segment- Program week of Desitination Segment- Destination Segment- Number of distinct people making this transition.&nbsp;The data I have has 3 relevant columns: a week, a segment, a UUID.Here is some testdata:&nbsp;&nbsp;&nbsp;let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdvLih03EAbgVzGzdoHuUm0DySokkFXAeKFrsAkeiJM8f9Q4kPiURPP7CA/Gi5nP3aWqUqn7zLt3L99/evPLh/75t9eXty/KueRr9cTWN3LdeCpZNeLqu4uhKOXd/Laff1WajDKKlHt5//ZZQx8wzAHDLozSlbKNYk+anM+acnKVqtXZacOusbq7F2ONKsY06sp7cmVYKq51mpdgY9U252i+NsIBwx8w5Nrihowpbsi1xY0T6xKfN/SB69DqwL3wASMJw8boVWyeVNazXnq7ai4q8s3VWYbZqtjv8hQ2tMwx3JC5jhsyT3FD5gduyPzA10XmB26cyA9Zc7gh+yluHFjbRQ/CjS95+tPr3/nP18/fJHyplu/yx/zmhz96/at/qh/yJbVStOZAVodrv5y7HrfaSBXd2I5oG/vbmKDGqnZh48R1LNYGNtSxyPIxKR2T4jEpHJP8Mckdk05k9LmrWeR1z7qH5q59PpKro1AxTlE0w9veR3a13dY5bCx6MGwsZgXYWOzzsLFYYdhY7GuwIdfWOc21pEqj5jrzI1rKHCz10odrqfvgw108cEPGAzdkjuHGIh45m6L6PL/lMGfa3BqVatz8q7DX1TqtvzYWsxNuLOIBG4t1gY1FTGHjQEwXsyRuyFkSN2TN4YbsY7gh+xhuHMiPf2vuv1kSF+zTgswvP4+aNjVFlUsi5xITj5xn2FIzrnD0+vb5EWwsagU3TlyH7B24IXsYHlNZs7ghaxY3ZM3ixoGYLvYnfF1kzeLXIfsPbviHqsUFeSfB+JGDqxS1m5UfnaZUc6DuDBuTWKnGdyuLGzIauCG7MW6ciIfMUtyQWYobenP2iHqozFlRvhDnQ6dsuFBLJYXhtfY63/VD3NidhHBp99wDl8wxaffEAJa2TwxwaffEAJd2TwxwaffEAJdktUc/v88UQ76bee5qvRKnPsgMU+epOinn1F2VwcZiL8CNE9exqFHYkPMGHlM5b+CGnDdwQ+5KuCE7OR7TbbeCr+ZL7fzY8+/98zcB/lnAPQuskjSOZmqkkiKTS/PIkbgF0qGU7GJuutT7gkON1cKixuOhCxcW0UjNextm3/NXG7TWU7Em01DK16Bi7Pa+/cDG4+EPFxYNrJmQqlU02/ccRYMO1+uMSpZDnMNLcn3+t4s7+d9VwMIinrCxyAzYkNFIyRU/bCGTr9woJlFOelBsOlrW2Rfb7u4FN3ZDCSxthxJc2o0SuLQbJXBp9/IBl3ZDLi7ttg1c2g25uLQ7UOCS3ODx7JaVihuye+LG4z6QSq5V1znK2l7IjXlaS0Eb6nMLUHN/NPPrNhqosboT2FhEFDYWvQ825BiKx5SfXZVFB2Zu84+P5IO6PljDg3INTMG3FkodwTi+iyhuPOYXLpy4E5lfnOeBLZRO14OJOSVEQ9lxIM/zn6HmaPvyZehzxiKisCEiCguLiJbsVXRz5stBkdPdUlHFzS3BDKVL8yrczsK4sYgobCwiChuy6nN3Y5TBpJOeMQ2zo7LPs5myVT0262y7fbkEG4sMw40D97LID9yQXRA35Nri6yJ3J9yQh3HckI+IcePxgTku7Ca8Ykdn2xxFp+b+EpKZZ6ZgKQ0dWRcdo7p9AQAbi/XFDVkzuCFrBjdkzeCGrBl8XWQfwo0D8dhO27i0exCMS7vTGy7tzly4dK4iZTXV1McYQc95oMw5ydZC8+jfKNbOqYbkuS1fczxnPM4nuCBrCTcW0eiRTdZ1/rjp5GKehrKdVI3RczLd+NsTBW7IWsIN/RhRWJCdBTdkbjQ3Gl8fs1C+zElrhEGlMVNNeuZnCoGjPm/IiOLGY0RxQUYUNx6fXLYerl8J8mT9PNLMTFdTGGkOssVwtdEbvfwQ9VdXgRqLLMeNxbrCxmJdYUN2DtxYrCy8LnIXxo0D8djuwri024VxabcL49LuGSou7fZzXFpUwojZWm7kVbrOo/OQzj5VCj2rkbtvRY3baoKNx10YF0SPhIVFJcGG7E5D+zpyq8SD7TTmHpYaRwqqaa+yraaW88bj+x1ceFwRXJDdbXgu1lVP4/qVUsd6UPY9EM+9KHfdsm63HxrHDXkexg15fsQN+SEK3JC1hhuyU+PGYm0Tj6ZipWquD7GOyzBpZoqJrroxRwpTbmOKGouaxa9D7n64IZ/b4MYiT+F4nFiXRZ7CxiJPYWNR+7Cxex+HSzOy7/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Segment = _t, UUID = _t, WeekCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SegmentName", type text}, {"LoyaltyId", type text}, {"ProgramWeekCombination", type text}})
in
    #"Changed Type"
&nbsp;&nbsp;&nbsp;The problem I run into is that I do not want to create a new table that has a combination of every week with every other week and then all the possible transitions. That would end up being way too much data. So I was hoping to use measures for this, but I can't write one good enough to do what I want it to.Any help is appreciated!&nbsp;

anonymous · Answer

The measures you want to segment have to evaluate only the rows with a price within the selected segments. Thus, assuming that SalesAmount is the original measure, you define the corresponding SalesRange measure that handles segmentation as follows:1234567891011121314SalesRange :=CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;[SalesAmount],&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( Sales[Price] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Ranges,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales[Price] &gt;= Ranges[Min Price]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; Sales[Price] &lt; Ranges[Max Price]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &gt; 0&nbsp;&nbsp;&nbsp;&nbsp;))

anonymous · Answer

I got so far as to get one row for every UUID with each destination and source, I just can't figure out how to group this by every combination of segments 😞 (and no, just changing the loyaltyID to a count does not work, the measure I use to create the segment source data does not play nice with that.

Here's the measure for reference:

_Segment in comparison period =

VAR SelectedWeekInPast = SELECTEDVALUE(ProgramWeekSelector[ProgramWeekCombination])

RETURN

CALCULATE(

MAX('rfm LoyaltySegmentation'[SegmentName]),

ALL('rfm LoyaltySegmentation'[SegmentName]),

'rfm LoyaltySegmentation'[ProgramWeekCombination] = SelectedWeekInPast

)

anonymous · Answer

Hey Lewis, I can't say I follow. You're talking about sales amounts but I only have segment names. The segmentation has already been done. My question is about what comes after.

v-lili6-msft · Answer

hi&nbsp;&nbsp;Anonymous&nbsp;
You could refer to this blog:
Dynamic Segmentation
If you still have the problem, please share your expected output based on the above sample data.
&nbsp;
Regards,
Lin

anonymous · Answer

Hey Lin,&nbsp;&nbsp;yeah i can understand this is confusing, it's been confusing me all day!&nbsp;Here is an example of what I want:&nbsp;&nbsp;So the user is able to choose a destination week (example: last week), and a source week, lets say the week before that, using the slicers. The table will then show all combinations of segments that people had in those weeks. For example, if there were only 3 people in my data, and this is what happened:&nbsp;"Johnny went from Leales to Baja Frequentia" and "Lizzy went from Novatos to En Riesgo", and "Benny also went from Novatos to En Riesgo"&nbsp;The resulting data should show:&nbsp;Destination Segment&nbsp; &nbsp; &nbsp; &nbsp;Source Segment&nbsp; &nbsp; &nbsp;Number of usersBaja Frequentia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Leales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1En Riesgo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Novatos&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;&nbsp;That's the idea.&nbsp;As you can see in the example I have come reasonable far with it. The problem is that the second column has to be a measure, and that measure currently uses a "MAX(destination segment)" which will always give Novatos.&nbsp;I hope this helps a bit. Sorry for confusing you!&nbsp;Jaap

Forum Discussion

Dynamic Segment Flow DAX measure

9 Replies

Dynamic Segmentation

Related content

Dynamic Segmentation

Dynamic measure filtering

|| Dynamic Segmentation : The formula ||

Dynamic Customer Segmentation

Dynamic Measure developement