Need help with DAX measure not displaying values until a value is selected in a slicer

[Paul198412]

Hi Community

I hope you can help me, I am stuck on a DAX measure and not sure how to get around this.

My problem is that I initially created the below script using SELECTEDVALUE in a SWITCH statement which is used for switching between currencies dependent on the selected country and if no country is selected it defaults back to leading currency (Euro) and this works fine in PBI desktop but as we have migrated this model to a tabular one and our business is running on SSAS 2015 SELECTEDVALUE doesn't exist in this version. Sample below

SWITCH with SelectedValue 1

SWITCH with SelectedValue 2

I have read up that I should rewrite the query using an IF statement in combination with HASONEVALUE and VALUES. So i have done this now as per script below, however now my script doesn't default to the leading currency when nothing is selected but just shows blank until a country selection is made instead of defaulting back to Euro? I have been struggling for days and can't think of what else to do please help

KPI YTD__ =
VAR Sweden =
IF ( HASONEVALUE ( Company[Country] ), VALUES ( Company[Country] ) = "Sweden" , VALUES(Category[Sub Categories]))
VAR Switzerland =
IF (
HASONEVALUE ( Company[Country] ),
VALUES ( Company[Country] ) = "Switzerland",COUNTROWS(VALUES(Company[Country]))
)
VAR Czech =
IF ( HASONEVALUE ( Company[Country] ), VALUES ( Company[Country] ) = "Czech" ,COUNTROWS(VALUES(Company[Country])))
VAR Euro =
IF (
HASONEVALUE ( Company[Country] ),
VALUES ( Company[Country] ) <> "Switzerland"
|| VALUES ( Company[Country] ) <> "Sweden"
|| VALUES ( Company[Country] ) <> "Czech"
,COUNTROWS(VALUES(Company[Country]))
)
RETURN
IF (
Sweden = TRUE (),
SWITCH (
VALUES ( Category[Sub Categories] ),
"Sales Colleague Retention Rate", FORMAT ( [YTD_AC - Sales Colleague Retention Rate %], "0.0%" ),
"Complaints p 1000 Service Visits", [YTD_AC - Complaints p 1000 Service Visits],
"Organic Revenue Growth", FORMAT ( [YTD_AC - Organic Revenue Growth %], "0.0%" ),
"Client Retention Rate", FORMAT ( [YTD_AC - Client Retention Rate %], "0.0%" ),
"Net Gain as % of Opening Portfolio", FORMAT ( [YTD_AC - Net Gain as a % of Opening Portfolio], "0.0%" ),
"Total Sales p Outdoor Sales FTE", FORMAT ( [YTD_AC - Total Sales p Outdoor Sales FTE], "kr#,##0.0" ),
"Sales Visits p Sales Day", [YTD_AC - Sales Visits p Sales Day],
"Revenue per productive service visit", FORMAT ( [YTD_AC - Revenue per productive visit], "kr#,##0.0" ),
"Gross Margin", FORMAT ( [YTD_AC - Gross Margin %], "0.0%" ),
"Sales Leads per service technician", [YTD_AC - Sales Leads Per Serv Tech],
"Job Product Rev growth", FORMAT ( [YTD_AC - Job Product Rev growth %], "0.0%" ),
"Gross Sales as % of Opening Portfolio", FORMAT ( [YTD_AC - Gross Sales as % of Opening Portfolio], "0.0%" ),
"Enquiries YoY Growth %", FORMAT ( [YTD_AC - Enquiries YoY Growth %], "0.0%" ),
"% Sales from New Products & Services", FORMAT ( [YTD_AC - Sales From New Products & Services %], "0.0%" ),
"Sales & Marketing Costs p 1 GBP GS&J&P", FORMAT ( [YTD_AC - Sales & Marketing Costs p 1 GBP GS&J&P], "kr#,##0.00" ),
"% Commission of GS&J&P", FORMAT ( [YTD_AC - Commission of GS&J&P %], "0.0%" ),
0
),
IF (
Switzerland = TRUE (),
SWITCH (
VALUES ( Category[Sub Categories] ),
"Sales Colleague Retention Rate", FORMAT ( [YTD_AC - Sales Colleague Retention Rate %], "0.0%" ),
"Complaints p 1000 Service Visits", [YTD_AC - Complaints p 1000 Service Visits],
"Organic Revenue Growth", FORMAT ( [YTD_AC - Organic Revenue Growth %], "0.0%" ),
"Client Retention Rate", FORMAT ( [YTD_AC - Client Retention Rate %], "0.0%" ),
"Net Gain as % of Opening Portfolio", FORMAT ( [YTD_AC - Net Gain as a % of Opening Portfolio], "0.0%" ),
"Total Sales p Outdoor Sales FTE", "CHf" & FORMAT ( [YTD_AC - Total Sales p Outdoor Sales FTE], "#,##0.0" ),
"Sales Visits p Sales Day", [YTD_AC - Sales Visits p Sales Day],
"Revenue per productive service visit", "CHf" & FORMAT ( [YTD_AC - Revenue per productive visit], "#,##0.0" ),
"Gross Margin", FORMAT ( [YTD_AC - Gross Margin %], "0.0%" ),
"Sales Leads per service technician", [YTD_AC - Sales Leads Per Serv Tech],
"Job Product Rev growth", FORMAT ( [YTD_AC - Job Product Rev growth %], "0.0%" ),
"Gross Sales as % of Opening Portfolio", FORMAT ( [YTD_AC - Gross Sales as % of Opening Portfolio], "0.0%" ),
"Enquiries YoY Growth %", FORMAT ( [YTD_AC - Enquiries YoY Growth %], "0.0%" ),
"% Sales from New Products & Services", FORMAT ( [YTD_AC - Sales From New Products & Services %], "0.0%" ),
"Sales & Marketing Costs p 1 GBP GS&J&P", "CHf" & FORMAT ( [YTD_AC - Sales & Marketing Costs p 1 GBP GS&J&P], "#,##0.00" ),
"% Commission of GS&J&P", FORMAT ( [YTD_AC - Commission of GS&J&P %], "0.0%" ),
0
),
IF (
Czech = TRUE (),
SWITCH (
VALUES ( Category[Sub Categories] ),
"Sales Colleague Retention Rate", FORMAT ( [YTD_AC - Sales Colleague Retention Rate %], "0.0%" ),
"Complaints p 1000 Service Visits", [YTD_AC - Complaints p 1000 Service Visits],
"Organic Revenue Growth", FORMAT ( [YTD_AC - Organic Revenue Growth %], "0.0%" ),
"Client Retention Rate", FORMAT ( [YTD_AC - Client Retention Rate %], "0.0%" ),
"Net Gain as % of Opening Portfolio", FORMAT ( [YTD_AC - Net Gain as a % of Opening Portfolio], "0.0%" ),
"Total Sales p Outdoor Sales FTE", FORMAT ( [YTD_AC - Total Sales p Outdoor Sales FTE], "Kč#,##0.0" ),
"Sales Visits p Sales Day", [YTD_AC - Sales Visits p Sales Day],
"Revenue per productive service visit", FORMAT ( [YTD_AC - Revenue per productive visit], "Kč#,##0.0" ),
"Gross Margin", FORMAT ( [YTD_AC - Gross Margin %], "0.0%" ),
"Sales Leads per service technician", [YTD_AC - Sales Leads Per Serv Tech],
"Job Product Rev growth", FORMAT ( [YTD_AC - Job Product Rev growth %], "0.0%" ),
"Gross Sales as % of Opening Portfolio", FORMAT ( [YTD_AC - Gross Sales as % of Opening Portfolio], "0.0%" ),
"Enquiries YoY Growth %", FORMAT ( [YTD_AC - Enquiries YoY Growth %], "0.0%" ),
"% Sales from New Products & Services", FORMAT ( [YTD_AC - Sales From New Products & Services %], "0.0%" ),
"Sales & Marketing Costs p 1 GBP GS&J&P", FORMAT ( [YTD_AC - Sales & Marketing Costs p 1 GBP GS&J&P], "Kč#,##0.00" ),
"% Commission of GS&J&P", FORMAT ( [YTD_AC - Commission of GS&J&P %], "0.0%" ),
0
),
IF (
Euro = TRUE (),
SWITCH (
VALUES ( Category[Sub Categories] ),
"Sales Colleague Retention Rate", FORMAT ( [YTD_AC - Sales Colleague Retention Rate %], "0.0%" ),
"Complaints p 1000 Service Visits", [YTD_AC - Complaints p 1000 Service Visits],
"Organic Revenue Growth", FORMAT ( [YTD_AC - Organic Revenue Growth %], "0.0%" ),
"Client Retention Rate", FORMAT ( [YTD_AC - Client Retention Rate %], "0.0%" ),
"Net Gain as % of Opening Portfolio", FORMAT ( [YTD_AC - Net Gain as a % of Opening Portfolio], "0.0%" ),
"Total Sales p Outdoor Sales FTE", FORMAT ( [YTD_AC - Total Sales p Outdoor Sales FTE], "€#,##0.0" ),
"Sales Visits p Sales Day", [YTD_AC - Sales Visits p Sales Day],
"Revenue per productive service visit", FORMAT ( [YTD_AC - Revenue per productive visit], "€#,##0.0" ),
"Gross Margin", FORMAT ( [YTD_AC - Gross Margin %], "0.0%" ),
"Sales Leads per service technician", [YTD_AC - Sales Leads Per Serv Tech],
"Job Product Rev growth", FORMAT ( [YTD_AC - Job Product Rev growth %], "0.0%" ),
"Gross Sales as % of Opening Portfolio", FORMAT ( [YTD_AC - Gross Sales as % of Opening Portfolio], "0.0%" ),
"Enquiries YoY Growth %", FORMAT ( [YTD_AC - Enquiries YoY Growth %], "0.0%" ),
"% Sales from New Products & Services", FORMAT ( [YTD_AC - Sales From New Products & Services %], "0.0%" ),
"Sales & Marketing Costs p 1 GBP GS&J&P", FORMAT ( [YTD_AC - Sales & Marketing Costs p 1 GBP GS&J&P], "€#,##0.00" ),
"% Commission of GS&J&P", FORMAT ( [YTD_AC - Commission of GS&J&P %], "0.0%" ),
0
)
)
)
)
)

[Anonymous · Accepted solution]

This code is so ugly that nobody would even want to look at it. Maintenance of this would be for people that do not have anything better to do. I'll tell you what you should do instead. Create a table, can be calculated, in which you'll store columns: Country, KPIName, Format. Use this table then to retrieve the pieces of info you need and your code will shrink by about 60-70%. What's more, you'll have a table to maintain, not code. How does that sound, huh?

[Paul198412]

Thanks for comments, will look into suggestions and let you know the outcome. 

[lbendlin]

Explain what you want to achieve with this variable

VAR Euro =
IF (
HASONEVALUE ( Company[Country] ),
VALUES ( Company[Country] ) <> "Switzerland"
|| VALUES ( Company[Country] ) <> "Sweden"
|| VALUES ( Company[Country] ) <> "Czech"
,COUNTROWS(VALUES(Company[Country]))
)

[Paul198412]

Hi Ibendlin

Thanks for your reply. I would like to display all Euro reporting countries as a default value at the end if neither of the non Euro companies (Czech, Sweden, Switzerland) are not selected. Basically it defaults now to blank shows nothing where i would like it to default to the last switch statement using this variable. 

[lbendlin]

Rewrite that variable to use && instead of || , or use NOT IN {}