cancel
Showing results for
Did you mean:
Highlighted
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

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors