Dynamic Segment Flow DAX measure

[Anonymous]

Hey all,

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.

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.

The data I have has 3 relevant columns: a week, a segment, a UUID.

Here is some testdata:

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"

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!

[v-lili6-msft]

hi  @Anonymous 

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.

Regards,

Lin

[Anonymous]

Hey Lin, 

yeah i can understand this is confusing, it's been confusing me all day!

Here is an example of what I want:

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:

"Johnny went from Leales to Baja Frequentia" and "Lizzy went from Novatos to En Riesgo", and "Benny also went from Novatos to En Riesgo"

The resulting data should show:

Destination Segment       Source Segment     Number of users

Baja Frequentia                Leales                      1

En Riesgo                         Novatos                  2

That's the idea.

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.

I hope this helps a bit. Sorry for confusing you!

Jaap

[v-lili6-msft]

hi  @Anonymous 

If possible, please share your sample pbix file for us have a test.

Regards,

Lin

[Anonymous]

Here is an example file for you, hope this link works:

Example file 

[v-lili6-msft]

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary resuly based on current row context.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Since _Segmentname Source is a measure, I think it is difficult to achieve in power bi.

Regards,

Lin

[Anonymous]

Yes I think what I wanted was not possible in PowerBI. I used another "solution" that just takes the value from 4 weeks ago, from 8 weeks ago and from 16 weeks ago in columns.

Thanks for thinking along!

Jaap

[Anonymous]

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]

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SalesRange := CALCULATE (     [SalesAmount],     FILTER (         VALUES ( Sales[Price] ),         COUNTROWS (             FILTER (                 Ranges,                 Sales[Price] >= Ranges[Min Price]                 && Sales[Price] < Ranges[Max Price]             )         ) > 0     ) )

[Anonymous]

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.