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:

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's avatar
lbendlin
Super User
1 year ago

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.

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

  • lbendlin's avatar
    lbendlin
    Super User
    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)
    )
  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

     

     

     

     

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

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

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

     

     

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

    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?

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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's avatar
    cn4422
    Helper V
    1 year ago
    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