Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi
want to know how many times 1 item is sold in combination with another item.
So, on the side of data we have this:
country year ordernr item
Germany 2019 123456 Sku12345
Germany 2019 123456 Sku23456
France 2019 298765 Sku34567
France 2018 298765 Sku34567
UK 2019 287654 Sku44556
UK 2019 287654 Sku45456
As a result we should have this as output:
country year ordernr item unique combination nr of items used
Germany 2019 123456 Sku12345 Sku12345 & Sku23456 2
Germany 2019 123456 Sku23456 none as the result is already 0
displayed in the field above
France 2019 298765 Sku34567 Sku34567 1
France 2018 298765 Sku34567 Sku34567 1
UK 2019 287654 Sku44556 Sku44556 & Sku45456 2
UK 2019 287654 Sku45456 none as the result is already 0
displayed in the field above
To get this result you check first country then year then order nr and as a result you count the number of unique combinations.
Any ideas??
gracias
M
Solved! Go to Solution.
HI, @emveha
You could use these two formula to create the measure
unique combination = IF ( CALCULATE ( MIN ( 'Table'[item] ), ALL ( 'Table'[item] ) ) = SELECTEDVALUE ( 'Table'[item] ), CALCULATE ( CONCATENATEX ( VALUES ( 'Table'[item] ), [item], "&", MAX ( 'Table'[item] ) ), ALL ( 'Table'[item] ) ), "none as the result is already displayed in the field above" )
nr of items used = IF(CALCULATE(MIN('Table'[item]),ALL('Table'[item]))=SELECTEDVALUE('Table'[item]),CALCULATE(COUNTA('Table'[item]),ALL('Table'[item])),0)
Result:
and here is my sample pbix file, please try it.
Best Regards,
Lin
HI, @emveha
You could use these two formula to create the measure
unique combination = IF ( CALCULATE ( MIN ( 'Table'[item] ), ALL ( 'Table'[item] ) ) = SELECTEDVALUE ( 'Table'[item] ), CALCULATE ( CONCATENATEX ( VALUES ( 'Table'[item] ), [item], "&", MAX ( 'Table'[item] ) ), ALL ( 'Table'[item] ) ), "none as the result is already displayed in the field above" )
nr of items used = IF(CALCULATE(MIN('Table'[item]),ALL('Table'[item]))=SELECTEDVALUE('Table'[item]),CALCULATE(COUNTA('Table'[item]),ALL('Table'[item])),0)
Result:
and here is my sample pbix file, please try it.
Best Regards,
Lin
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |