Create a relationship between Fact Table Values and Measure Values

[cn4422]

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:

[Anonymous]

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.

[cn4422]

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. 🙂

[lbendlin]

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.

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-...

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/1447...

[cn4422]

@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! ^^

[lbendlin]

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-...

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/1447...

[cn4422]

Hi @lbendlin ,

finally, here is the sample Data.

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

[lbendlin]

see attached

[cn4422]

@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!

[lbendlin]

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.

[cn4422]

---

@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.)?

[lbendlin]

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.

[cn4422]

---

@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...

[lbendlin]

[cn4422]

---

@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?

[lbendlin]

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

[cn4422]

---

@lbendlin wrote:

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

---

It will be 17 countries in total.

You may want to refactor your source data to include the country attribute in the fact table.

---

I'm getting Data from a direct query and I'm building the country-reference in the measure with certain attributes from this direct query (account name, campaign name).

Example:

    MEASURE '0_MeasureTable'[Sum Spend FB AT] = CALCULATE(

            SUM('GA, FB, Spend'[spend]),

            'GA, FB, Spend'[datasource] = "Main FB",

            'GA, FB, Spend'[account_name] = "Main FB" && SEARCH(

                "[AT]",

                'GA, FB, Spend'[campaign],

                1,

                0

            )

        )

I would like to include the country attribute in the fact table, but I don't know how this could work with a direct query.

That's why I was headed towards the approach with the static country table and then somehow link the measures to it.

[lbendlin]

In that case you are indeed staring down the barrel of having to use the SWITCH statement 17 times.

[cn4422]

---

@lbendlin wrote:

In that case you are indeed staring down the barrel of having to use the SWITCH statement 17 times.

---

You are awesome - thank you so much! It's working exactly as I had in mind! 😍

(The downside with 17 countries is the performance, it's taking ages to load the visual 😁)

[cn4422]

---

@lbendlin wrote:

In that case you are indeed staring down the barrel of having to use the SWITCH statement 17 times.

---

Ok, thanks for clarifying this. 😄

I'm still trying to figure out the correct SWITCH statement for this case.

In the meantime I was wondering if there is a way with the IF-measure you provided that it only shows the relevant row and no row if it does not suite. I tried it this way, but it's still showing an (empty) row:

CPL FB =

IF(SELECTEDVALUE(Countries[Country])="DE",DIVIDE([Sum Spend DE FB],[Sum Leads DE FB],0))

[cn4422]

@lbendlin 

One more question regarding the Switch-Statement: Is there a way to "hide" empty rows?

In this example, I have two SWITCH-Statements

CPL Sklik Switch = SWITCH(SELECTEDVALUE(Countries[Country]),

"CZ",DIVIDE([Sum Spend Sklik CZ CurrConv],[SumLeads Sklik CZ],0), Blank()

    )

CPL Bing Switch = SWITCH(SELECTEDVALUE(Countries[Country]),

"AT",DIVIDE([Sum Spend Bing Ads AT],[SumLeads Bing AT],0),Blank()

    )

And I want it to be only shown in the correct Country-row.

I tried to add "BLANK()" to the statement, which didn't work.

I tried to put a filter over the visual with ">0" and I checked if "empty rows" was deactivated...

But I couldn't manage to hide the rows with no values in the matrix...

Any idea if this is possible?