Summarize table showing Empty Values

[Anonymous]

Hello!

I am trying to summarize a table that contains customer ID data, Order ID, Item ID, and a ranking that classifies each customer's orders (from the first to the last order) - (I created a fake table to preserve my data):

When I summarize this table to find the item bought per client in the first order (

SUMMARIZE(filter('Summary Clientes','Summary Clientes Pedidos'[Rank]=1)

, I achieve this:

However, my "problem" is when I summarize this table looking to find the items purchased in the second purchase. The client ID "3" has just one order, and when I summarize this power bi does not show this ID.

How can I create a table that shows all the second orders per customer and, if any customer ID has only one purchase, Power Bi shows me an empty cell, like this:

I really appreciate your help.

Tks in advance.

Lucas

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous ,

You could create a measure by the following formula：

Newtable=
var _summarize=SUMMARIZE(FILTER('Summary Clientes',[Rank Order]=2),[Client ID],[Item ID])
var _ClientID=SELECTCOLUMNS(_summarize,"ID",[Client ID])
var _ID=DATATABLE("ID",INTEGER,{{1},{2},{3}})
var _union=DATATABLE("Client ID",INTEGER,"Item ID",INTEGER,{{1, },{2, },{3, }})
var _except=EXCEPT(_ID,_ClientID)
return UNION(_summarize,FILTER(_union,[Client ID] in _except))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @Anonymous 

Can you

1. Share the first table in text-tabular format, so that the contents can be copied? Just copy from Excel/PBI and paste here

2. Show the full code you are using to generate the summarized tables  

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.