Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Paul198412
Regular Visitor

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

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

 

Paul198412_0-1597995149540.png

 

SWITCH with SelectedValue 1

Paul198412_1-1597995149560.png

 

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
)
)
)
)
)

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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?

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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?

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

lbendlin
Super User
Super User

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]))
)

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. 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.