Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
dear all,
I like to find combinations in sales where i want to find all categories that someone bought on a day for every row in the sales table.
i have a table like this:
Customer | Date | Visitkey | Category |
1 | 01-01-22 | 1/01-01-22 | a |
1 | 01-01-22 | 1/01-01-22 | b |
2 | 01-01-22 | 2/01-01-22 | c |
2 | 01-01-22 | 2/01-01-22 | a |
1 | 02-01-22 | 1/02-01-22 | a |
1 | 02-01-22 | 1/02-01-22 | a |
2 | 02-01-22 | 2/02-01-22 | b |
2 | 02-01-22 | 2/02-01-22 | a |
2 | 02-01-22 | 2/02-01-22 | c |
and per visit i wish to determine all the categorynames per visit
the dimension table looks like this:
Category | categoryname |
a | a1 |
b | b1 |
c | c1 |
d | d1 |
and the result should look like this:
Customer | Date | Visitkey | Category | Result |
1 | 01-01-22 | 1/01-01-22 | a | a1, b1 |
1 | 01-01-22 | 1/01-01-22 | b | a1, b1 |
2 | 01-01-22 | 2/01-01-22 | c | a1, c1 |
2 | 01-01-22 | 2/01-01-22 | a | a1, c1 |
1 | 02-01-22 | 1/02-01-22 | a | a1 |
1 | 02-01-22 | 1/02-01-22 | a | a1 |
2 | 02-01-22 | 2/02-01-22 | b | a1, b1, c1 |
2 | 02-01-22 | 2/02-01-22 | a | a1, b1, c1 |
2 | 02-01-22 | 2/02-01-22 | c | a1, b1, c1 |
there is a relationship from category to sales table between category column from 1 - *
Solved! Go to Solution.
HI @VV24,
You can try to use the following calculated column formula to directly lookup and concatenate dimension table values based on the current category.
Result=
VAR list =
CALCULATETABLE (
VALUES ( Table[Category] ),
FILTER ( Table, [Customer] = EARLIER ( Table[Customer] ) )
)
RETURN
CONCATENATEX ( FILTER ( dimension, [Category] IN list ), [categoryname], "," )
Regards,
Xiaoxin Sheng
HI @VV24,
You can try to use the following calculated column formula to directly lookup and concatenate dimension table values based on the current category.
Result=
VAR list =
CALCULATETABLE (
VALUES ( Table[Category] ),
FILTER ( Table, [Customer] = EARLIER ( Table[Customer] ) )
)
RETURN
CONCATENATEX ( FILTER ( dimension, [Category] IN list ), [categoryname], "," )
Regards,
Xiaoxin Sheng
thanks, its working! and lets say if you have a *-* relationship between those two tables, is there a way to solve it then?
Hi @VV24 ,
Please try the steps given below :
1) Create a 1:M relationship between the dim and fact tables on the category field.
2) Using the relationship defined above, create a new calculated column on the fact table.
3) Finally, create another calculated column using the column created above that will give you the desired result.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |