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.
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?
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |