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
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 I
Super User I

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 I
Super User I

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors