Returns only first row with lookupvalue

[Laufer_Israel]

Hi all,

I have a table as can be seen below and I would like to see only the first row for "Total sales 2018" column.

It is important to note that "Total sales 2018" is a data which has been taken from another table with LOOKUPVALUE DAX.

Does it is possible to see only the first row for each customer?? (in order to avoid duplication)

Many thanks in advance.

Code	Name	Product	Quantity	Total sales 2018
1	One	PP	2	220
1	One	SS	3	220
1	One	YY	4	220
1	One	MM	9	220
2	Two	PP	1	167
2	Two	SS	4	167
2	Two	YY	7	167
2	Two	MM	2	167
3	Three	PP	3	701
3	Three	SS	1	701
3	Three	YY	3	701
3	Three	MM	8	701

[Jimmy801]

Hello @Laufer_Israel ,

Apply a grouping with the columns that have distinct values. (Code, Name, Total sales). Here an example

let
	Source = #table
	(
		{"Code","Name","Product","Quantity","Total sales 2018"},
		{
			{"1","One","PP","2","220"},	{"1","One","SS","3","220"},	{"1","One","YY","4","220"},	{"1","One","MM","9","220"},	{"2","Two","PP","1","167"},	{"2","Two","SS","4","167"},	
			{"2","Two","YY","7","167"},	{"2","Two","MM","2","167"},	{"3","Three","PP","3","701"},	{"3","Three","SS","1","701"},	{"3","Three","YY","3","701"},	{"3","Three","MM","8","701"}

					}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Name", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Total sales 2018", Int64.Type}}),
    Group = Table.Group(ChangeType, {"Code", "Name", "Total sales 2018"}, {{"AllRows", each _, type table [Code=number, Name=text, Product=text, Quantity=number, Total sales 2018=number]}})
in
	Group

Copy paste this code to the advanced editor to see how the solution works. You can apply it by copying a part of my code to your query, or I can help you to apply a custom function

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Laufer_Israel]

Thank you Jimmy for your prompt comment.

Since the data in my question is only an example of my issue, your recomendation is'nt valid on this case.

Actually what I would like to do, is to add another column that will show only the first row from "Total sales 2018" for each customer and will leave the rest of the rows for each customer blank.\

Does it possible??

[Laufer_Israel]

The right column it is what I am looking for.

Code	Name	Product	Quantity	Total sales 2018	Total sales 2018
1	One	PP	2	220	220
1	One	SS	3	220
1	One	YY	4	220
1	One	MM	9	220
2	Two	PP	1	167	167
2	Two	SS	4	167
2	Two	YY	7	167
2	Two	MM	2	167
3	Three	PP	3	701	701
3	Three	SS	1	701
3	Three	YY	3	701
3	Three	MM	8	701

[Anonymous]

Hi @Laufer_Israel ,

Are you able to this kind of lookup? I yes. May I know please what DAX did you use?

Thank you 🙂

[Jimmy801]

Hello @Laufer_Israel 

if you need to do this in Power Query, use this slightly changed query

let
	Source = #table
	(
		{"Code","Name","Product","Quantity","Total sales 2018"},
		{
			{"1","One","PP","2","220"},	{"1","One","SS","3","220"},	{"1","One","YY","4","220"},	{"1","One","MM","9","220"},	{"2","Two","PP","1","167"},	{"2","Two","SS","4","167"},	
			{"2","Two","YY","7","167"},	{"2","Two","MM","2","167"},	{"3","Three","PP","3","701"},	{"3","Three","SS","1","701"},	{"3","Three","YY","3","701"},	{"3","Three","MM","8","701"}

					}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Name", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Total sales 2018", Int64.Type}}),
    Group = Table.Group(ChangeType, {"Code", "Name", "Total sales 2018"}, {{"AllRows", each _, type table [Code=number, Name=text, Product=text, Quantity=number, Total sales 2018=number]}}),
	AddIndex = Table.TransformColumns
	(
		Group,
		{{"AllRows", each Table.AddIndexColumn(_,"Index",1 )}}
	),
	AddColumn = Table.TransformColumns
	(
		AddIndex,
		{{"AllRows", each Table.AddColumn(_,"Total Sales 2018 new",(add)=> if add[Index]=1 then add[#"Total sales 2018"] else null )}}
	),
    DeleteOtherColumns = Table.SelectColumns(AddColumn,{"AllRows"}),
    ExpandAllRows = Table.ExpandTableColumn(DeleteOtherColumns, "AllRows", {"Code", "Name", "Product", "Quantity", "Total sales 2018", "Total Sales 2018 new"}, {"Code", "Name", "Product", "Quantity", "Total sales 2018", "Total Sales 2018 new"})
in
    ExpandAllRows

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Laufer_Israel]

Guys,

As I mentioned, the table I shared with you is only an example for my data. (it is not even close to how my data looks like...)

Unfortunatlly, due to confidential matter I can't share my data with you.

Back to your proposals, it isn't solve my issue..

@v-xicai - your proposal dosen't solve it since the rank column returns totally diffrent figures, so the "1" in the second DAX is not relevant.

@Jimmy801 - I'm totally confuse from your proposal. my Advances Editor shows diffrence figures since as I noted above, my data looks diffrence in comparison to my exaple..

Anyway, many thanks for your willing to assist with this frustrating issue.

Other thoughts??

[Jimmy801]

Hello @Laufer_Israel 

just copy my entire code into a NEW BLANK query and check out how my solution works.

Jimmy

[Laufer_Israel]

Thank you @Jimmy801.

I forgot to indicate that I have also a column with dates in my data.

Actually what I am trying to do is to visualize the total sales for 2018 with a dependency of a slicer.

Means: If I will choose on the slicer a period between Jan to Apr (for an example) ... I want to see only the sales of those month.

Currently it can't be possible since the SUM function taking the multiple sales (i.e: if customer apear in 4 rows it summarize his sales).

Any other suggestion? maybe thare is some DAX that can solve it??

Many thanks in advance,

Israel

[Jimmy801]

Hello @Laufer_Israel 

as you can see in my solution, the new column shows the sales only once. So assuming for every row, where the same amount is stated, also the month is applied and therefore you can sum this column.

Question... why does in the column total Sales show up the value on every row in first place. This can be never the real data, can it?

Jimmy

[Laufer_Israel]

The "Total Sales" column is the total sales for 2018 per customer which has been taken from another table (with LOOKUPVALUE).

My original table contains only sales per customer per specific product. Now, I would like to add to this table the total sales for a specific year, per customer, and this is the source of my issue... (that lookupvalue DAX returns multiple figures).

[Laufer_Israel]

Here's another demonstration of my data:

					Taken from another table
Month	Code	Name	Product	Sales per product	Total sales 2018	Solution needed
January	1	One	PP	2	220	220
January	1	One	SS	3	220
January	1	One	YY	4	220
January	1	One	MM	9	220
January	2	Two	PP	1	167	167
January	2	Two	SS	4	167
January	2	Two	YY	7	167
January	2	Two	MM	2	167
January	3	Three	PP	3	701	701
January	3	Three	SS	1	701
January	3	Three	YY	3	701
January	3	Three	MM	8	701
February	1	One	PP	2	220	220
February	1	One	SS	3	220
February	1	One	YY	4	220
February	1	One	MM	9	220
February	2	Two	PP	1	167	167
February	2	Two	SS	4	167
February	2	Two	YY	7	167
February	2	Two	MM	2	167
February	3	Three	PP	3	701	701
February	3	Three	SS	1	701
February	3	Three	YY	3	701
February	3	Three	MM	8	701

[Jimmy801]

Hello @Laufer_Israel 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @Laufer_Israel 

i don't get the point. Its basically the same data, just that you added a column and renamed another. I slightly adapted my code, and that's it. But the concept is always the same

let
	Source = #table
	(
		{"Month","Code","Name","Product","Sales per product","Total sales 2018"},
		{
			{"January","1","One","PP","2","220"},	{"January","1","One","SS","3","220"},	{"January","1","One","YY","4","220"},	{"January","1","One","MM","9","220"},	
			{"January","2","Two","PP","1","167"},	{"January","2","Two","SS","4","167"},	{"January","2","Two","YY","7","167"},	{"January","2","Two","MM","2","167"},	
			{"January","3","Three","PP","3","701"},	{"January","3","Three","SS","1","701"},	{"January","3","Three","YY","3","701"},	{"January","3","Three","MM","8","701"},	
			{"February","1","One","PP","2","220"},	{"February","1","One","SS","3","220"},	{"February","1","One","YY","4","220"},	{"February","1","One","MM","9","220"},	
			{"February","2","Two","PP","1","167"},	{"February","2","Two","SS","4","167"},	{"February","2","Two","YY","7","167"},	{"February","2","Two","MM","2","167"},	
			{"February","3","Three","PP","3","701"},	{"February","3","Three","SS","1","701"},	{"February","3","Three","YY","3","701"},	{"February","3","Three","MM","8","701"}

					}
	),

    ChangeType = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Name", type text}, {"Product", type text}, {"Sales per product", Int64.Type}, {"Total sales 2018", Int64.Type}, {"Month", type text}}),
    Group = Table.Group(ChangeType, {"Month", "Code", "Name", "Total sales 2018"}, {{"AllRows", each _, type table [Code=number, Name=text, Product=text, Quantity=number, Total sales 2018=number]}}),
	AddIndex = Table.TransformColumns
	(
		Group,
		{{"AllRows", each Table.AddIndexColumn(_,"Index",1 )}}
	),
	AddColumn = Table.TransformColumns
	(
		AddIndex,
		{{"AllRows", each Table.AddColumn(_,"Total Sales 2018 new",(add)=> if add[Index]=1 then add[#"Total sales 2018"] else null )}}
	),
    DeleteOtherColumns = Table.SelectColumns(AddColumn,{"AllRows"}),
    ExpandAllRows = Table.ExpandTableColumn(DeleteOtherColumns, "AllRows", { "Month", "Code","Name", "Product", "Sales per product", "Total sales 2018", "Total Sales 2018 new"}, {"Month", "Code","Name", "Product", "Sales per product", "Total sales 2018", "Total Sales 2018 new"})
in
    ExpandAllRows

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-xicai]

Hi @Laufer_Israel ,

You can create columns like DAX below.

Rank = CALCULATE(COUNT(Table1[Code]),FILTER(Table1, Table1[Code]<=EARLIER(Table1[Code])))
 
Total sales 2018_New=IF([Rank]=1, [Total sales 2018], BLANK())

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.