cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Conference

Power Platform Community Conference

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

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors