cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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

Bogas_0-1593760024492.png

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:

Bogas_1-1593760336133.png

 

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 
https://drive.google.com/file/d/1fZlYGai2Au3c8_Qc8jzZZJGYDh8xd5Ew/view?usp=sharing

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
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:

Annotation 2020-07-06 144831.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Show related items but not my item

can you post your data in text format




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
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
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
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
https://drive.google.com/file/d/1Zqd-IVGS3EzXgrleqEpu6JToxFlfF-dx/view?usp=sharing

Highlighted
Solution Sage
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:

Annotation 2020-07-06 144831.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Highlighted
Helper I
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 😃

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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