Forum Discussion
Create a relationship between Fact Table Values and Measure Values
- 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 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:
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.
In that case you are indeed staring down the barrel of having to use the SWITCH statement 17 times.
- 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))
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.
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))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?
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.
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