Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi @Anonymous ,

 

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
Anonymous
Not applicable

Hi @Anonymous ,

 

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!
Anonymous
Not applicable

Tank you alot for you help @Anonymous  big hug and have a nice day 😃

vanessafvg
Super User
Super User

can you post your data in text format




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.