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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gushal
Frequent Visitor

Show if a record exists in a another table

Hi,

 

I am new at Power BI and need some help. Following are my issue:

 

We have an opportunity table and one Sales order table. One opportunity can have several sales orders. 

 

I want to display in a matrix, how many of the opportunites that has a one or several sales orders connected to it, i.e. I don´t care if there are several sales orders, I just want to display if the opportunity has any sales orders or none at all. 

 

I want to display it in percentage of total count of opportunities. 

 

For example: In one store there are 5000 opportunities and 8000 sales orders. But only 3000 of these opportunities have connected sales orders, which means 60% of the opportunities have one or several sales orders:

 

gushal_0-1607413388482.png

 

 

How do I create this?

 

Thanks in advance

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @gushal 

Based on the information you currently provide, I made a  sample here.

Measure = 
var Measure1 = CALCULATE(DISTINCTCOUNT('Sales order table'[opportunities]),ALLEXCEPT('Sales order table','Sales order table'[Store]))
var Measure2 = CALCULATE(DISTINCTCOUNT('opportunity table'[opportunities]),ALLEXCEPT('opportunity table','opportunity table'[Store]))
return  Measure2/Measure1

 

If it doesn't meet you current requirement,please share information about your two tables.

 

Best Regards,
Community Support Team _ Eason

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

Hi, @gushal 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

 

Best Regards,
Community Support Team _ Eason

v-easonf-msft
Community Support
Community Support

Hi, @gushal 

Based on the information you currently provide, I made a  sample here.

Measure = 
var Measure1 = CALCULATE(DISTINCTCOUNT('Sales order table'[opportunities]),ALLEXCEPT('Sales order table','Sales order table'[Store]))
var Measure2 = CALCULATE(DISTINCTCOUNT('opportunity table'[opportunities]),ALLEXCEPT('opportunity table','opportunity table'[Store]))
return  Measure2/Measure1

 

If it doesn't meet you current requirement,please share information about your two tables.

 

Best Regards,
Community Support Team _ Eason

 

AllisonKennedy
Super User
Super User

You must have matching values in both tables - is there an Opportunity ID in the Sales Order table? How do you know if they are in both tables? 

 

Please share your sample data or at least the column and table names with some sample values.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.