Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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.
Hi @Igor_M
looks like they are sorted in alphabetical order. Or is this just by chance?
The above output table does indeed seam to be sorted but in fact it does not need to be.
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?
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.
Hi @Igor_M
You can try the following query
EVALUATE
SUMMARIZE (
'Table',
'Table'[ItemCode],
"ItemDescription", MIN ( 'Table'[ItemDescription] )
)
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
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
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.
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?
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
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
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
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!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |