Get distinct list of item codes and first description

[Igor_M]

Hi,

I have a table with two columns - ItemCode and ItemDescription. An item code may appear more than once in the ItemCode column. Also, the descriptions in the ItemDescription column can vary for a given item code.

So I'd like to get a filtered table with distinct item codes in the ItemCode column and the first available description for that item code (in alphabetical order) in the ItemDescription column.

This must be in DAX (not Power Query).

To give an example, let's assume this is my source table (please notice that K400-Printers appear twice and that K300 and K500 have different descriptions):

ItemCode	ItemDescription
K200	Monitors
K300	Computers
K400	Printers
K400	Printers
K300	Computers
K300	PCs
K500	Laptops
K500	Notebooks

This should be the output:

ItemCode	ItemDescription
K200	Monitors
K300	Computers
K400	Printers
K500	Laptops

[Igor_M]

To give a bit more background. The actual table has a lot more columns, there is an index column composed based on database code (three digits) and an item index (eg. 010#K200). It is in fact a dimension table in that semantic model, holding item codes and item descriptions from 10 different databases. The pair code+description should be consistent across all the 10 databases but unfortunatelly it isn't, hence this request.

I'm looking for a calculated table - I need to use the output in Power Automate.

[tamerj1]

Hi @Igor_M 

looks like they are sorted in alphabetical order. Or is this just by chance?

[Igor_M]

The above output table does indeed seam to be sorted but in fact it does not need to be.

[tamerj1]

@Igor_M 

Is adding an Index column via a native query or using power query an option for you? Is there a date column or any column that defines the sort order of the table? Are you looking for a DAX measure or a calculated table or just a query?

[Igor_M]

To give a bit more background. The actual table has a lot more columns, there is an index column composed based on database code (three digits) and an item index (eg. 010#K200). It is in fact a dimension table in that semantic model, holding item codes and item descriptions from 10 different databases. The pair code+description should be consistent across all the 10 databases but unfortunatelly it isn't, hence this request.

I'm looking for a calculated table - I need to use the output in Power Automate.

[tamerj1]

Hi @Igor_M 

You can try the following query 

EVALUATE
SUMMARIZE (
'Table',
'Table'[ItemCode],
"ItemDescription", MIN ( 'Table'[ItemDescription] )
)

[Igor_M]

Hi @tamerj1 - perfect, this is exactly what I wanted.

On hindsight, I realised it would be better to return description for an item code with most entries in the Transacts table.

With the code below I get a list of all item codes (ItemCode, ItemDescription) with the number of transactions in the Transacts table. I added IGNORE because I need to return all items, even if they don't have any transactions.

EVALUATE
	VAR _Table1 = SUMMARIZECOLUMNS(
		Items[ItemCode],
		Items[ItemDescription],
		FILTER(
			Items,
			'Items'[ItemCode] <> BLANK()
		),
		"TransactsCount", IGNORE(COUNTROWS(Transacts))
	)

	RETURN
		_Table1
	

Of course this table has all the possible combinations of item codes and their descriptions - item codes are not dictinct.

What I don't know is how to filter/aggregate this table now, so that item codes are distinct and that item description for an item is the one with most transactions.

I'd really appreciate your help @tamerj1 

[tamerj1]

@Igor_M 

If you wish to see the count you my evaluate T2 instead of T3

DEFINE
VAR T1 =
SUMMARIZE (
Items,
Items[ItemCode],
Items[ItemDescription],
"@Count", COUNTROWS ( Items )
)
VAR T2 =
TOPN ( T1, [@Count] )
VAR T3 =
GROUPBY ( T2, [ItemCode], [Description] )

EVALUATE
T3

[Igor_M]

Hi @tamerj1 , thank you for a prompt reply. However the query returns only one row - the one with most transactions. What I meant, is that I wanted to retrieve all item codes and their descriptions (regardless of the fact whether there are any transactions recorded for an item in the Transacts table) but the description for an item should be assigned based on the number of transactions recorded in the Transacts table - item description with the highest number of transactions should be selected.

[tamerj1]

@Igor_M 

Do you mean the same item shall be repeated along with the most frequent description as many time as tge item exists in the original table? If so may I ask what is the reason for having a table with duplicate rows? Or there would be other columns included in the query that beaks the duplication?

[Igor_M]

Hi @tamerj1 

I tried to keep the initial question simple and thus probably oversimplified the structure of the tables.

Below is extended version of the Items table, the TransactsCount column, which shows the number of rows in the Transacts (fact) table with a given ItemID, does not actually exist in the model, I've only added it here to keep it simple.

Items					TransactsCount
ItemID	ItemCode	ItemDescription	DatabaseID
010#K200	K200	Monitors	010		0
010#K300	K300	Computers	010		4
011#K300	K300	PCs	011		5
010#K400	K400	Printers	010		2
011#K400	K400	Inkjet Printers	011		1
010#K500	K500	Laptops	010		1
011#K500	K500	Notebooks	011		1

The desired output:

ItemCode	ItemDescription		Comment
K200	Monitors		Only one item description
K300	PCs		PCs because more transacts with this description (i.e. ItemID)
K400	Printers		Printes because more transacts with this description (i.e. ItemID)
K500	Laptops		A tie, pick either description

As per your questions - the output table should have two columns:

1) ItemCode - distinct list of all item codes regardless of whether there are any transactions recorded for the item code,

2) ItemDescription - description for the item, if there is more than one description available in the Items table for a given item code, the description for the item (ItemID) with the highest number of transactions should be used.

I hope this helps.

I also prepared a PBIX model: ItemsDesc.pbix

[v-yaningy-msft]

Hi, @Igor_M 

Thanks for @tamerj1 and @VahidDM  help. It looks like their responses have been of some help to you, you can refer to their responses, and if they don't meet your needs, you can refer to the following.

DAX:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[ItemCode] = EARLIER ( 'Table'[ItemCode] )
    ),
    'Table'[ItemDescription],
    ,
    ASC,
    Dense
)

FilterRank = IF(SELECTEDVALUE('Table'[Rank]) = 1 , 1 , 0) 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[tamerj1]

@Igor_M 

With the sample file you have provided, I realized that I misunderstood your request. The solution provided by @VahidDM is perfect being simple and efficient while handling ties at the same time. That was all made possible by intelligently utilizing a window function. 

[VahidDM · Accepted solution]

Hi @Igor_M 

Try this DAX code:

DEFINE
	VAR _A = ADDCOLUMNS(
		'Items',
		"SumTra", CALCULATE(SUM('Transacts'[Amount]) + 0)
	)

EVALUATE
	FILTER(
		ADDCOLUMNS(
			_A,
			"R", RANK(
				DENSE,
				_A,
				ORDERBY(
					[SumTra],
					DESC
				),
				PARTITIONBY('Items'[ItemCode])
			)
		),
		[R] = 1
	)

OUTPUT:

Note: Amount column in the Transacts table had a text type and I changed that to number
File attached (Check DAX Query View)

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Igor_M]

Hi,

thank you all for your valuable input and insightful responses. 

In the meantine I also managed to get the desired result based on@tamerj1's answer. This is the code:

EVALUATE
	SUMMARIZE(
		Items,
		Items[ItemCode],
		"ItemDesc", SELECTCOLUMNS(
			TOPN(
				1,
				ADDCOLUMNS(
					VALUES(Items[ItemDescription]),
					"@TransactsCount", CALCULATE(COUNTROWS(Transacts))
				),
				[@TransactsCount],
				DESC,
				Items[ItemDescription],
				ASC
			),
			Items[ItemDescription]
		)
	)

If I understand correctly, there will be a problem with the above code should the TOPN ever return more than 1 row. For this reason I am going to go with @VahidDM's code.

As a side note, could someone kindly explain to me why my above code works fine but the following does NOT - Computers is returned for K200 instead of Monitors?

EVALUATE
	VAR __tab1 = SUMMARIZE(
		Items,
		Items[ItemCode]
	)
	VAR __tab2 = ADDCOLUMNS(
		__tab1,
		"ItemDesc", SELECTCOLUMNS(
			TOPN(
				1,
				ADDCOLUMNS(
					VALUES(Items[ItemDescription]),
					"@TransactsCount", CALCULATE(COUNTROWS(Transacts))
				),
				[@TransactsCount],
				DESC,
				Items[ItemDescription],
				ASC
			),
			Items[ItemDescription]
		)
	)
	RETURN
		__tab2

Once more - thank you all for your help!