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:

[lbendlin]

You can only hide them if they are empty throughout.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[cn4422]

---

@lbendlin wrote:

You can only hide them if they are empty throughout.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

---

This is how it should look like and here is the

Sample Data

[lbendlin]

Same problem as before.  You would need to provide yet another reference table with the list of measure names as a column. Then you would have to add that column to the rows area of the matrix, and condense all your current measures into one.

[cn4422]

@lbendlin Thank you! 

[lbendlin · Accepted solution]

CPL FB =

SWITCH(SELECTEDVALUE(Countries[Country]),

"DE",DIVIDE([Sum Spend DE FB],[Sum Leads DE FB],0),

"AT",DIVIDE([Sum Spend AT FB],[Sum Leads AT FB],0)

)

[cn4422]

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

[lbendlin]

You create new sample data without sensitive information.

[cn4422]

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

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