Forum Discussion

cn4422's avatar
cn4422
Helper V
2 years ago
Solved

Create a relationship between Fact Table Values and Measure Values

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 ...
  • lbendlin's avatar
    lbendlin
    1 year ago
    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's avatar
cn4422
Helper V
1 year ago
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's avatar
lbendlin
Super User
1 year ago

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