Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all!
My question is, how to create something like groups for different items into different orders?
For example, I have the data source:
Order# | OrderDate | Item | Qty |
1 | 01.01.2019 | apple | 3 |
2 | 02.01.2019 | apple | 3 |
2 | 02.01.2019 | orange | 2 |
3 | 03.01.2019 | apple | 2 |
3 | 03.01.2019 | banana | 3 |
3 | 03.01.2019 | orange | 4 |
3 | 03.01.2019 | pear | 2 |
I want to show:
The first card/BI component TOP item: Apples - were sold three times
-----photo
Second: The TOP pair for Apple is Orange - were sold together two times per period.
Third:
In addition, the apples were sold with products: banana, pear - together one time per period.
Of course, I have much more data (For example: Apples were sold with 20 different items together 1000 times per period), so I need to create the filter where I can choose count of orders when products were sold together.
- more than X times (orders) per period or between X-Y.
In my example, If I don't want to see products that were sold less than twice with my TOP(apple) I change slicer condition and the report shows me just Orange.
Can you help me?
Many thanks!
Solved! Go to Solution.
hi, @volesh
After my research, you could try this way:
Step1:
Add a rank measure by Item
Rank = RANKX(ALLSELECTED(Table1[Item]),CALCULATE(COUNTA(Table1[Item])),,,Dense)
Step2:
Create a filter by measure for third card.
filter by = var _top1=CALCULATE(MAXX(TOPN(1,ALLSELECTED(Table1[Item]),[Rank],ASC),[Item])) var _orderno=CALCULATETABLE(VALUES(Table1[Order#]),FILTER(ALLSELECTED(Table1),Table1[Item]=_top1)) var _table =FILTER(SUMMARIZE(Table1,Table1[Order#],Table1[Item]),Table1[Order#] in _orderno) return COUNTAX(FILTER(_table,[Item]<>_top1),[Item])
Step3:
Now create three cards.
First card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 1
Second card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 2
Third card: Darg Item and count measure into card visual and then drag filter by measure into visual level filter and set is more than X times (orders) per period or between X-Y.
Result:
and here is pbix file, please try it.
Best Regards,
Lin
hi, @volesh
After my research, you could try this way:
Step1:
Add a rank measure by Item
Rank = RANKX(ALLSELECTED(Table1[Item]),CALCULATE(COUNTA(Table1[Item])),,,Dense)
Step2:
Create a filter by measure for third card.
filter by = var _top1=CALCULATE(MAXX(TOPN(1,ALLSELECTED(Table1[Item]),[Rank],ASC),[Item])) var _orderno=CALCULATETABLE(VALUES(Table1[Order#]),FILTER(ALLSELECTED(Table1),Table1[Item]=_top1)) var _table =FILTER(SUMMARIZE(Table1,Table1[Order#],Table1[Item]),Table1[Order#] in _orderno) return COUNTAX(FILTER(_table,[Item]<>_top1),[Item])
Step3:
Now create three cards.
First card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 1
Second card: Darg Item and count measure into card visual and then drag rank measure into visual level filter and set is 2
Third card: Darg Item and count measure into card visual and then drag filter by measure into visual level filter and set is more than X times (orders) per period or between X-Y.
Result:
and here is pbix file, please try it.
Best Regards,
Lin
Thank you very much @v-lili6-msft
I marked your solution.
If you are still looking at my post, I will add one more question:
How can I use the measure filter by for each item on a card?
For example on a second card I want to show:
Item #, the number of orders an item, and the number of shared purchases.
Order# | OrderDate | Item | Qty |
1 | 01.01.2019 | apple | 3 |
2 | 02.01.2019 | apple | 3 |
2 | 02.01.2019 | orange | 2 |
3 | 03.01.2019 | orange | 2 |
4 | 03.01.2019 | apple | 3 |
4 | 03.01.2019 | orange | 4 |
4 | 03.01.2019 | pear | 2 |
Expected result:
Card#1: Apple - sold 3 times
Card#2: orange - sold 3 times, 2 - the number of shared purchases with apple.
Card#3: pear - sold 1 time, 0 - the number of shared purchases with apple, 0 - the number of shared purchases with any other products
@v-lili6-msft
Thanks a lot for your solution.
But unfortunately, it isn't enough for me...
In my data, I have a few items for the first card, because of the equal count of items.
1. Card #1
I want to show just one. Do I need to add a ranking (new_Rank) taking into account the current ranking?
for ex:
source
Order# | OrderDate | Item | Qty |
1 | 01.01.2019 | apple | 3 |
2 | 02.01.2019 | apple | 3 |
2 | 02.01.2019 | orange | 2 |
3 | 03.01.2019 | orange | 2 |
4 | 03.01.2019 | apple | 3 |
4 | 03.01.2019 | orange | 4 |
4 | 03.01.2019 | pear | 2 |
Rank:
Item | Cnt of Orders | Current Rank | New Rank |
apple | 3 | 1 | 1 |
orange | 3 | 1 | 2 |
Card #2.
The same question. There may be many values in rank 2. On a card, I want to show just one but I need to be able to show all of them.
It is worth noting, that item "orange" has rank 1 but has to be on Card#2, because "apple" is on the first card.
I tried to change DAX (rank)
=RANKX(ALLSELECTED('test data'[Item_code]), (CALCULATE(DISTINCTCOUNT('test data'[order_ID])) + int(CALCULATE(MIN('test data'[OrderDate])))/100000 ) ,,DESC,Dense )
and it works, but just with [OrderDate]... and I dont know how to do DAX with [Item] - 'text' data type. In addition, I have items code like - "# 12qwer", "$asd 1" and other.
Can you help me one more time?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |