Calculating values that appear in two tables | Forum

Resolved · Low Priority · Version 2016

Wed 29 Jan 2020, 12:56 Delegate Lou said...

Lou has attended:
Power BI Modelling, Visualisation and Publishing course
Excel VBA Introduction course

Calculating values that appear in two tables

Hi,

I've a new report where I have exported several files from our database. It's to show alumni stats so every report has the alumni URN in it.

I have the main alumni export with their biographical information.
I also have additional exports which show event registrations and volunteering participation.

My main export contains no duplicate rows.
In the others, the URN may appear multiple times, depending on how often the individual has attended an event etc.

My event and volunteering exports have a relationship with the main export so I am able to show the gender/age etc of the volunteers/event attendees.

I also want to show the number of event attendees who are volunteers. These tables aren't and can't be linked. And I don't seem to be able to find a formula that will allow me to show this in a visual.

Apologies if this is a silly question but any suggestions gratefully received!

Thanks,

Thu 30 Jan 2020, 09:33 Trainer Jens said...

RE: Calculating values that appear in two tables

Hi Lou,

Thank you for the forum question.

To show information in visuals across many tables Power Bi must understand how the data are linked to each other. It can be done in relationships, query merge, or in DAX functions.

The Lookupvalue DAX function can be a solution. It can link tables based on multiple fields. It means that if a number of fields in one table can can identify specific records in another table (also called multiple fields keys), then you will not need a relationship in your data model to explain the connection between the tables.

A question like this can be difficult to answer everything depends on the data model.

I do not know if it is possible for you, but I am happy to look at your data model, if you can send me your file.

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 5 Feb 2020, 12:44 Delegate Lou said...

RE: Calculating values that appear in two tables

Hi Jens,

Thank you; I will pop that over to you in a moment!

Thanks,

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Stay Updated and Engage with the Community

Power BI is continually evolving, with new features and updates being released regularly. Stay informed about the latest enhancements by regularly checking the Power BI blog and community forums. Engaging with the Power BI community provides opportunities to learn from others, share your experiences, and get insights into best practices.

View all Power BI hints and tips