Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a table like this:
ID | Product | Sub-product |
1 | A | AAAA1 |
2 | B | BBBB1 |
3 | A | AAAA1 |
4 | D | DDDD1 |
5 | B | BBBB2 |
6 | A | AAAA3 |
7 | A | AAAA2 |
8 | B | BBBB1 |
9 | D | DDDD3 |
10 | A | AAAA1 |
11 | C | CCCC1 |
12 | A | AAAA5 |
What I want is a table that contains the distinct values in "Sub-product" column and also the corresponding value in the "Product" column.
The result is:
Distinct Product | Distinct Sub-product |
A | AAAA1 |
B | BBBB1 |
D | DDDD1 |
B | BBBB2 |
A | AAAA3 |
A | AAAA2 |
D | DDDD3 |
C | CCCC1 |
A | AAAA5 |
So, all distinct values of the "Sub-product" column are only shown. I want to use this table as the main table to build relations and filter several tables.
How can I do this?
Thanks.
Solved! Go to Solution.
@Anonymous , Try like this in DAX
New table =
Summarize(Table,Table[Product], Table[Sub Product])
Also refer the pwoer query way : https://www.youtube.com/watch?v=kU2M1LmNvNo
https://www.youtube.com/watch?v=vHuhbvYCiNc
I recommend you do this in Power Query. This is what I get with two steps - Remove Other Columns, and Remove Duplicates.
M code is here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYSAwVIrViVYyAvKcQBgIICLGGGpMgDwXEAYCiIgpki4jsIgZki5jsIg5kghEjQWGXZZIJkN0GRpgWG8IcrUzCAMBVMgISZWpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, #"Sub-product" = _t]),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Product", "Sub-product"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates"
You could do it in DAX though:
THis is because ALL() returns distinct values unless you are using it on the entire table. Here it is just columns.
New Table = ALL('DataTable'[Product], 'DataTable'[Sub-product])
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous , Try like this in DAX
New table =
Summarize(Table,Table[Product], Table[Sub Product])
Also refer the pwoer query way : https://www.youtube.com/watch?v=kU2M1LmNvNo
https://www.youtube.com/watch?v=vHuhbvYCiNc
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
97 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |