Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
The data behind is as below (MasterTable):
Member Number | Date Added | Product Description Before | Product Description After |
MN0001 | 01/10/2022 | AAA | BBB |
MN0001 | 30/09/2022 | AAA | BBB |
MN0002 | 20/09/2022 | CCC | DDD |
MN0003 | 21/09/2022 | AAA | AAA |
I want to create a visual that describes the change of product distribution i.e. the distinct count of member number by product description before, compared to the distinct count of member number by product description after.
It is easy to create two bar chart visuals as below:
The left bar chart is the distinct count of member number by product description before while the bar chart on the right is the distinct count of member number by product description after. However this is not very user friendly as the user want to see the difference between the change i.e. like below:
For example, product description AAA is the count of distinct member number group by product description after minus the count of distinct member number group by product description before where product description is AAA i.e. 2-1 = 1
I have tried creating a measure like this:
The 'count previous product test1' table is a calculated table as below:
Note that the above data and visuals are mocked.
It would be great if someone can help me to solve this issue. Thank you in advance 🙂
@Anonymous , Based on what I got
Create a new table Product
Product = distinct(Union(distinct(Table[Product Description Before]) ,distinct(Table[Product Description After])) )
The join with both Product Description Before and Product Description After].
Assume after join is inactive
Then have measure likes
Products Before = count(Table[Product Description Before])
Products After = calculate(Count(Table[Product Description After]) , userelationship(Table[Product Description After], Product [Product Description Before]))
Use these measure type of measures to calculate
Hi @amitchandak, thanks for your suggestion. This works except that I need count distinct Member Number group by product description i.e.
. Could you help me with how to put userelationship() within sumx() for Product After please?
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |