Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Extract segmented data

Hi 

I have a data table with survey data of the same respondents over time. Data looks like

Campaign | Respondent | Category
Jan2017  | 001a1      | Blue
Jan2017 | 001bb | Orange
Feb2017 | 001a1 | Blue
Feb2017 | 001bb | Blue
Feb2017 | 0012b | Orange

Now I would like to produce a table or a visual on my dashboard that shows how many were Blue in the first campaign and what this segment was in the following campaigns. So if I look at Blue in Jan2017 I would learn that of the 1 that one was Blue in Jan2017 1 was still blue in Feb2017. If I selected Orange in Jan2017 I would learn that none of that segment were Orange again in Feb2017.

 

I hope it makes sense and you are able to help me.

 

br, Simon

5 REPLIES 5
Eric_Zhang
Employee
Employee


@SimonKibsgaard wrote:

Hi 

I have a data table with survey data of the same respondents over time. Data looks like

Campaign | Respondent | Category
Jan2017  | 001a1      | Blue
Jan2017 | 001bb | Orange
Feb2017 | 001a1 | Blue
Feb2017 | 001bb | Blue
Feb2017 | 0012b | Orange

Now I would like to produce a table or a visual on my dashboard that shows how many were Blue in the first campaign and what this segment was in the following campaigns. So if I look at Blue in Jan2017 I would learn that of the 1 that one was Blue in Jan2017 1 was still blue in Feb2017. If I selected Orange in Jan2017 I would learn that none of that segment were Orange again in Feb2017.

 

I hope it makes sense and you are able to help me.

 

br, Simon


@SimonKibsgaard

Try 2 measures

 

count of respondent in current campaign = IF(HASONEVALUE('table'[Campaign])&&HASONEVALUE('table'[Category]),COUNT('table'[Respondent]),BLANK())


count of respondent in next campaign =
IF (
    HASONEVALUE ( 'table'[Campaign] ) && HASONEVALUE ( 'table'[Category] ),
    IF (
        ISBLANK (
            COUNTROWS (
                FILTER (
                    CALCULATETABLE ( 'table', NEXTMONTH ( 'table'[Campaign] ) ),
                    'table'[Respondent] IN VALUES ( 'table'[Respondent] )
                )
            )
        ),
        0,
        COUNTROWS (
            FILTER (
                CALCULATETABLE ( 'table', NEXTMONTH ( 'table'[Campaign] ) ),
                'table'[Respondent] IN VALUES ( 'table'[Respondent] )
            )
        )
    ),
    BLANK ()
)

Capture.PNG

 

Thnx @Eric_Zhang, but that doesnt quite do the trick

 

1. there is not always a month between campaigns (but they can have a start date added as a new column, if that would help)

2. We don't know if there are 2 or more campaigns.

 

So one of the insights I would like was that "4 respondents were Blue in the first campaign, of those 3 were Blue in second and 1 was Blue in the third campaign"

 

I guess a sankey diagram would be the ideal way to visualize, but I am not sure how to prepare data or present the diagram.

 

br, Simon


@SimonKibsgaard wrote:

Thnx @Eric_Zhang, but that doesnt quite do the trick

 

1. there is not always a month between campaigns (but they can have a start date added as a new column, if that would help)

2. We don't know if there are 2 or more campaigns.

 

So one of the insights I would like was that "4 respondents were Blue in the first campaign, of those 3 were Blue in second and 1 was Blue in the third campaign"

 

I guess a sankey diagram would be the ideal way to visualize, but I am not sure how to prepare data or present the diagram.

 

br, Simon


@SimonKibsgaard

Well, could you post more specific sample data for your scenario?

Hi @Eric_Zhang

Yes, you're right.

Here is some better example data and a png of the diagram I would like to produce:

 

DATA:

CampaignRespondentdateCategory
Cmp-1d77e22fd3ffbc37ea7dd63c20dea9f111-01-2017 00:00Blue
Cmp-18a1ed1e1c09617517f4bff8a6c2c16b10-01-2017 00:00Blue
Cmp-19bd1d31136d45405e1d1b5cca86b4c610-01-2017 00:00Blue
Cmp-1ece18a9a2462ab2728248b778f15cd310-01-2017 00:00Blue
Cmp-1686e3d48a600abd2db8b65c73e0e3c418-01-2017 00:00Orange
Cmp-17611c2f508d3a912d94b6f7e70eebee09-01-2017 00:00Orange
Cmp-2d77e22fd3ffbc37ea7dd63c20dea9f113-02-2017 00:00Blue
Cmp-28a1ed1e1c09617517f4bff8a6c2c16b10-02-2017 00:00Blue
Cmp-29bd1d31136d45405e1d1b5cca86b4c608-03-2017 00:00Blue
Cmp-2ece18a9a2462ab2728248b778f15cd308-03-2017 00:00Grey
Cmp-27611c2f508d3a912d94b6f7e70eebee09-02-2017 00:00Lime
Cmp-2686e3d48a600abd2db8b65c73e0e3c409-02-2017 00:00Orange
Cmp-3d77e22fd3ffbc37ea7dd63c20dea9f103-04-2017 00:00Blue
Cmp-38a1ed1e1c09617517f4bff8a6c2c16b04-04-2017 00:00Blue
Cmp-39bd1d31136d45405e1d1b5cca86b4c604-04-2017 00:00Blue
Cmp-3ece18a9a2462ab2728248b778f15cd331-03-2017 00:00Grey
Cmp-37611c2f508d3a912d94b6f7e70eebee12-04-2017 00:00Orange
Cmp-3686e3d48a600abd2db8b65c73e0e3c406-04-2017 00:00Orange

 

DIAGRAM:

sankey.png

Anyone with and idea for how to solve this? Maybe sankey diagram isn't the right solution?

 

Fingers crossed hoping for a bright idea...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.