Need help undrstanding how to create a table with distrinct rows with conditions.

[kushmaa]

I am trying to do something for work and I was hoping someone could help me understand what I need to do. (New to Power BI)

So I have 2 separate tables in Excel, one table is a list of active Subscribed clients we have, and one list of unsubscribed clients.

The main difference between the lists is that the Subscribed clients is already distrinct, whereas the unsubscribed list has recorded every time a client has unsubsribed. (for example, Client A might have unsubscribed yesterday, but resubscribed today, therefore in the subscribed list he's got a column called "subscribed date" and in the unsubscribed list he has columns called  "subscribed date" AND "unsubscribed date", and in that way he could have several records of being unsubscribed. Visual examples further below) 

I need to make a table that shows the total clients (of all time) whether they are unsubscribed or subscribed. I tried appending the two tables, but that doesnt work as the list is not of distinct clients. 

I added a column in the data query to each table called "Subscribed?" which has a yes or a no.

Examples of both lists:

Subscribed list
Subscribed?	Subscribed	Unsubscribed	Name
Yes	6/1/1000	N/A	John
Unubscribed list
Subscribed?	Subscribed	Unsubscribed	Name
No	1/1/1000	2/1/1000	Jane
No	2/1/1000	3/1/1000	Jane
No	1/1/1000	2/1/1000	John
No	3/1/1000	5/1/1000	John

I need a table that shows clients off all time but only shows them once, taking the rows that have "Yes" in "Subscribed?" as priority, and that shows the most recently subscribed "unsubscribed" row. so the result would be:

Final List
Subscribed?	Subscribed	Unsubscribed	Name
Yes	6/1/1000	N/A	John
No	2/1/1000	3/1/1000	Jane

Of course there are other columns like client ID, phone number etc, but none of those matter in the filtering process, I just need the most recent info from the unsubscribed, and the current info in the subscribed.

I should mention I'm not allowed to change the excel files, so all of this has to happen from within Power BI.

Thanks in advance for anyone willing to take a look at this!

[v-lili6-msft · Accepted solution]

Hi@ kushmaa

Yes, you need to add new name column:

Column 4 = IF(Subscribed[Unubscribed.Name] =BLANK(),Subscribed[Name],Subscribed[Unubscribed.Name]) 

And then use SUMMARIZE to create a table

Table = SUMMARIZE(Subscribed,Subscribed[Column 4],Subscribed[Column 3],"Subscribed",MAX(Subscribed[Column]),"UnSubscribed",MAX(Subscribed[Column 2]))

Result:

Best Regards,

Lin

[v-lili6-msft]

Hi@ kushmaa

After my research , you can do these follow my steps like below:

Step 1:

Get data , then merge two table

Step 2:

Expand the table

Step 3:

Close&Apply

Step 4:

Add three calculate column

Column = IF(Subscribed[Subscribed?]="Yes",Subscribed[Subscribed],Subscribed[Unubscribed.Subscribed])
Column 2 = IF(Subscribed[Subscribed?]="Yes",Subscribed[Unsubscribed],Subscribed[Unubscribed.Unsubscribed])
Column 3 = IF(Subscribed[Subscribed?]="Yes","Yes","No")

Step 5:

Use SUMMARIZE Function to create new table

Table = SUMMARIZE(Subscribed,Subscribed[Unubscribed.Name],Subscribed[Column 3],"Subscribed",MAX(Subscribed[Column]),"UnSubscribed",MAX(Subscribed[Column 2]))

Step 6:

Rename column

Result:

Here is DEMO, please try it

https://www.dropbox.com/s/seenk26gd5twoiu/Need%20help%20undrstanding%20how%20to%20create%20a%20table...

Best Regards,

Lin

[kushmaa]

@v-lili6-msft  thank you so much for your reply. 

This seemed to work well until I tested what would happen if I changed John's name to Brook in the subscribed table. The result I was hoping for was 3 entries, now taking account Brook, and two unsubscribed clients, John and Jane.

Basically what's happening is that if the name does not exist in the unsubscribed list, it will not come up at all because it will not load in the "Unsubscribed.Name" column in the merged table. That would be a problem for new clients who have never unsubscribed yet. 

But I think the easy fix would be to make it so if "Unsubscribed.Name" is blank, it should draw the name from "Name" instead. I'm not familiar with DAX, however, could you type out the code for me?

Thanks in advace.

[kushmaa]

New to power BI, and need to do something for work.

I have two tables in excel, one table for subscribed clients, the other for unsubscribed clients. The main difference between the tables is that the subscribed list is already distinct, where as the unsubscribed list outlines every single time a client has unsubscribed. In power BI data query I added a column to each table called "Subscribed?" which has a Yes in the subscribed list, and a No in the unsubscribed list.

Example:

Subscribed Table
Subscribed?	Subscribed	Unsubscribed	Name
Yes	6/1/1000	N/A	John
Unubscribed Table
Subscribed?	Subscribed	Unsubscribed	Name
No	1/1/1000	2/1/1000	Jane
No	2/1/1000	3/1/1000	Jane
No	1/1/1000	2/1/1000	John
No	3/1/1000	5/1/1000	John

as you can see John has resubscribed, so he's on the main list, but a record of when he has unsubscribed in the past is kept in the unsubscribed list. I need to create a new table that shows a distrinct list of all clients, taking "Yes" in the "Subscribed?" column as priority, and takes the most recent subscribed date in case the client has not resubscribed. 

the final result should be:

Final Table
Subscribed?	Subscribed	Unsubscribed	Name
Yes	6/1/1000	N/A	John
No	2/1/1000	3/1/1000	Jane

as you can see it only shows Jane and John once. of course there are other columns in the tables but they are not relevant for filtering purposes. if lets say the phone number of jane changed, I want the final table to have the most recent phone number recorded in her unsubsribed row. 

I should mention I am not allowed to modify the Excel files, so this has to happen in Power BI. The reason I need this is because I can't establish a relationship with other tables in my project if I were to just append the two lists, because the client names are not distinct. 

thanks in advance to anyone willing to take a look at this!