cancel
Showing results for
Did you mean:
Helper I

Show related items but not my item

Hello all

I having a truble to achive this calculation in powerbi i need some help please.

My exemple data

i select drink and i need to catch the Tikets ID belong to drink and build a table with % of the drink is more comuns to show up with drink selected please have a look on exemple below.

Exemple:

Water was selected and belongs to ticket 1, 2, 4, 5, and 8.

with what informaiton i need to build a table to show me all drinks where tikets in (1, 2, 4, 5, and 8 )
and drinks != from my selection (Water)
counting how meany itens i have (2 column on my table) divide by divide by total of drinks from first table (26)

!! what i stargle is when i select water my table only show me water insted of another drinks. =(

you can get this excel via this link

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

Re: Show related items but not my item

Hi @Bogas ,

Create 3 measures as below:

``````My table =
var _ticketID=CALCULATETABLE(VALUES(Table1[Tikets ID]),FILTER(ALL('Table1'),'Table1'[Drinkname]=SELECTEDVALUE(Table2[Drink Name])))
var _drinkname=CALCULATETABLE(VALUES(Table1[Drinkname]),FILTER(ALL('Table1'),'Table1'[Tikets ID] in _ticketID&&'Table1'[Drinkname]<>SELECTEDVALUE(Table2[Drink Name])))
Return
IF(MAX('Table1'[Drinkname]) in _drinkname,MAX('Table1'[Drinkname]),BLANK())``````
``````count =
var _ticketID=CALCULATETABLE(VALUES(Table1[Tikets ID]),FILTER(ALL('Table1'),'Table1'[Drinkname]=SELECTEDVALUE(Table2[Drink Name])))
var _drinkname=CALCULATETABLE(VALUES(Table1[Drinkname]),FILTER(ALL('Table1'),'Table1'[Tikets ID] in _ticketID&&'Table1'[Drinkname]<>SELECTEDVALUE(Table2[Drink Name])))
Return
COUNTX(FILTER('Table1','Table1'[Drinkname]=MAX('Table1'[Drinkname])&&'Table1'[Drinkname] in _drinkname&&'Table1'[Tikets ID] in _ticketID),'Table1'[Drinkname])``````
``percent = 'Table1'[count]/CALCULATE(COUNTROWS('Table1'),ALL(Table1))``

And you will see:

Best Regards,
Kelly
6 REPLIES 6
Highlighted
Super User II

Re: Show related items but not my item

can you post your data in text format

Proud to be a Super User!

Highlighted
Helper I

Re: Show related items but not my item

Hi i left a link where you can access the excel i did as a exemple 😃 thank you for your help

Highlighted
Super User II

e my Re: Show related items but not my item

aha see my pbix attached

i created a copy of the table, linked on ticket number and then you can select from the slicer on table one where water , that links to the other table on ticket and returns all the values for those ticks with water

Proud to be a Super User!

Highlighted
Helper I

Re: e my Re: Show related items but not my item

thank you for your help but i can not duplicate my table =( this reason im gething stack.
my main table contain around 700GB of compress data in powerBi (18.667.088 rowns) i can not duplicated this =(

Please can you try to achive the same result base on 1 table
or
Table 1 = Tickets ID and Prodocts ID
Table 2 = Prodoct ID and Prodoct Name
This way i no need to duplicate my main table 😃

link below with new excel with 2 tables

Highlighted
Solution Sage

Re: Show related items but not my item

Hi @Bogas ,

Create 3 measures as below:

``````My table =
var _ticketID=CALCULATETABLE(VALUES(Table1[Tikets ID]),FILTER(ALL('Table1'),'Table1'[Drinkname]=SELECTEDVALUE(Table2[Drink Name])))
var _drinkname=CALCULATETABLE(VALUES(Table1[Drinkname]),FILTER(ALL('Table1'),'Table1'[Tikets ID] in _ticketID&&'Table1'[Drinkname]<>SELECTEDVALUE(Table2[Drink Name])))
Return
IF(MAX('Table1'[Drinkname]) in _drinkname,MAX('Table1'[Drinkname]),BLANK())``````
``````count =
var _ticketID=CALCULATETABLE(VALUES(Table1[Tikets ID]),FILTER(ALL('Table1'),'Table1'[Drinkname]=SELECTEDVALUE(Table2[Drink Name])))
var _drinkname=CALCULATETABLE(VALUES(Table1[Drinkname]),FILTER(ALL('Table1'),'Table1'[Tikets ID] in _ticketID&&'Table1'[Drinkname]<>SELECTEDVALUE(Table2[Drink Name])))
Return
COUNTX(FILTER('Table1','Table1'[Drinkname]=MAX('Table1'[Drinkname])&&'Table1'[Drinkname] in _drinkname&&'Table1'[Tikets ID] in _ticketID),'Table1'[Drinkname])``````
``percent = 'Table1'[count]/CALCULATE(COUNTROWS('Table1'),ALL(Table1))``

And you will see:

Best Regards,
Kelly
Highlighted
Helper I

Re: Show related items but not my item

Tank you alot for you help @v-kellya-msft  big hug and have a nice day 😃

Announcements

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021