Connecting data tables to link cost

[Anonymous]

Hi,

I have this very simple problem. I want to make a graph that has my systems as the axis and then the cost as values. However, the information comes from two different sheets:

ID sheet:

System sheet: 

I have a one to many relationship:

But my graph ends up looking like this:

What do I do wrong? I don't think that it should be necessary to lookup the cost from the ID sheet to each row of the system sheet in DAX.

Br. Trond

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Yes, you can use the related function in the measure. The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship. for more details, please refer to https://docs.microsoft.com/en-us/dax/related-function-dax

And, you can also build a bidirectional relationship between ID table and system table without using new meaure:

It will work too:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thanks @amitchandak and @AlB ,

The new measure worked. However, I'm in doubt of what the right thing to do is? Should I rearrange the tables or use the quick measure and why?

Br. Trond 

[amitchandak]

@Anonymous , to me the model seems correct. When you want to use one side table and realtion is single direction , related can help.

[Anonymous]

When you say related can help, do you mean the new measure? 

Br. Trond 

[amitchandak]

@Anonymous , Try if this can work, a new measure

sumx(system, related(id[cost]))

[AlB]

Hi @Anonymous 

Filters won't propagate from the many to the 1 side in a unidirectional relationship by default. You could build a measure that uses RELATED to retrieve the cost from the ID table. But it'd be best if you reorganize the data model. Ideally you'd have something from a dimension table in the x-axis of the chart.

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers