topic Re: Create a relationship between Fact Table Values and Measure Values in DAX Commands and Tips

Create a relationship between Fact Table Values and Measure Values

cn4422 — Fri, 11 Oct 2024 10:57:08 GMT

Hi,

I have attached a screenshot of what I would like to accomplish.

The idea is to create CPL (Cost-per-Lead) for different countries and display them over a certain time period.

I have set up the different CPL-Calculations with DAX Measures.

I have the countries in a fact table.

I would now like to create a table "CPL per Country" with the columns "Country" and "CPL".

Then create a relation between Country (fact table) and Country (CPL per Country table).

The challenge I'm facing is that I cannot (or don't know how to) insert the CPL for each Country as a measure into the new "CPL per Country" table.

I hope I could explain what I would like to accomplish. 😄

Thanks for your help/input in advance!

Edit:
Solution that worked for me:

Create a static table with the country names

Create the switch statement for countries:

CPL Switch =

SWITCH(SELECTEDVALUE(Countries[Country]),

"DE",[CPL DE],

"AT",[CPL AT],

etc.

)

Select a matrix visual and put
Countries into row
Date into column
Switch statemento into value
Edit the visual under "values" and select "show values on rows."

Result:

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Fri, 30 Aug 2024 20:02:13 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Create a relationship between Fact Table Values and Measure Values

Anonymous — Mon, 02 Sep 2024 06:46:19 GMT

Hi cn4422

Thanks for the reply from lbendlin .

cn4422 , based on your current description, are you trying to put the Country column and measure into one table and create a relationship with the Country column in the original table? If so, please refer to the following test. If this sample data is structurally different from the one you are using, as lbendlin said, we need you to provide some sample data so that we can help you better. Currently I am using sample data from Power BI Desktop.

1. Create a measure as follows

sum = CALCULATE(SUM(financials[Profit]), ALLEXCEPT(financials, financials[Country]))

2. Create a calculated table as follows

sum per country = SUMMARIZE(financials, financials[Country], "sum", [sum])

3. Create a relationship between two tables

Best Regards,
Yulia Xu

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

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Wed, 04 Sep 2024 19:21:09 GMT

Thanks for your help. I'm currently building another report which is more urgent - but I hope I will get back to this one by the end of the week to see if your suggestion will do the trick! 🙂

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Mon, 30 Sep 2024 11:08:50 GMT

Hi Anonymous ,

so, finally I'm back with building this report!

Thanks for your suggestions and the file. I checked everything and I think I didn't explain accurtely enough what my challenge is. In your sample report you have the "profit" with respective countries which are part of the "financials" table. I understand how you get relations then.

In my case, however, there are countries, but actually I think it's not relevant. I could also make a new table with a countries column. What I would like to achieve is to then get the measures into this table.

I'm not sure about providing sample data, that's why I'm tryining to explain it as simple as possible in this way. 🙂

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Mon, 30 Sep 2024 12:31:04 GMT

What I would like to achieve is to then get the measures into this table.

You cannot/should not create calculated columns from measures. They don't have the same context.

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Tue, 01 Oct 2024 11:38:30 GMT

lbendlin

You cannot/should not create calculated columns from measures. They don't have the same context.

I suppose I'm not familiar enough with PBI yet to grasp this concept...

Also, since I'm not sure how to provide data without giving away sensitive information in this case, I'm trying to illustrate my challenge with the following short video in PBI, in which I can hopefully explain what I'm trying to achieve 😄

Video

Thanks in advance! ^^

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Tue, 01 Oct 2024 11:49:39 GMT

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Tue, 01 Oct 2024 11:53:00 GMT

lbendlin The problem is that I don't know how to provide sample data without giving away sensitive information in this case.

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Tue, 01 Oct 2024 11:55:12 GMT

You create new sample data without sensitive information.

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Thu, 03 Oct 2024 15:26:58 GMT

Hi lbendlin ,

finally, here is the sample Data.

Sample Data
(You don't need to have dropbox to download the file)

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Thu, 03 Oct 2024 17:50:28 GMT

see attached

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Fri, 04 Oct 2024 09:10:07 GMT

lbendlin Thanks for your effort, looks almost perfect. 🙂

Two questions:

1. How did you manage to arrange the quarters like you did?

When I do it, they are arranged differently...

2. What I still don't know is how to link the measures "CPL GA DE" and "CPL FB DE" to an overall "Country DE"... so that it looks like this...

Thanks again for your help, very much appreciated!

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Fri, 04 Oct 2024 12:33:18 GMT

1. Read about "Show values on rows"

2. You would need to use a disconnected reference table if you really want to show the country columns.

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Mon, 07 Oct 2024 06:02:22 GMT

lbendlin wrote:
1. Read about "Show values on rows"

Thanks that worked perfectly! 🙂

lbendlin wrote:
2. You would need to use a disconnected reference table if you really want to show the country columns.

I did some research and found out about "calculation groups", which is a very interesting topic I'm certainly going to delve into...

however, I couldn't really find anything about "disconnected reference table" to connect the measures from my example to a new entity "country"...

Any idea where I can find out more about this topic (video/posts/etc.)?

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Mon, 07 Oct 2024 19:17:36 GMT

Just create a static table with the country names. Put that table's columns in the row area. In each measure read the current value and return blank if the country code doesn't match.

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Tue, 08 Oct 2024 15:32:31 GMT

lbendlin wrote:
Just create a static table with the country names. Put that table's columns in the row area.

I've done that.

In each measure read the current value and return blank if the country code doesn't match..

Unfortunaetly, I don't know how to do this... to establish a link from the measure to the country table...

Currently it looks like this...

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Tue, 08 Oct 2024 23:46:50 GMT

Re: Create a relationship between Fact Table Values and Measure Values

cn4422 — Wed, 09 Oct 2024 07:45:57 GMT

lbendlin wrote:

Thanks again for your help - that's great, I can see not how you did it!

What if I want to add more countries... The idea is that the measure should only be placed in the row if the country-code is a match, if not, then it should simply be omitted.

What would be the best approach here?

My first idea was to maybe try it with a switch-statement?

Re: Create a relationship between Fact Table Values and Measure Values

lbendlin — Wed, 09 Oct 2024 11:08:05 GMT

How many countries? How many platforms? You may want to refactor your source data to include the country attribute in the fact table.