Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Menu
Search
0
LOGIN

Home
Training
Consulting
Articles & Blog
Videos
Books
Tools
About

Accept cookies to show this video. Cookies Policy
Allow all cookies

SWITCH – DAX Guide

SWITCH: Returns different results depending on the value of an expression.
https://dax.guide/switch/

Oct 28, 2021

LinkedIn Twitter Facebook Email

Subscribe

Comments

You need to login in order to comment this video.

Login Or register

Subscribe

Notify of

Please login to comment

4 Comments

Inline Feedbacks

View all comments

Evan Belkin

2 months ago

Hello SQLBI team,

I have used Contoso database to reproduce the issue that I am getting.

In a nutshell, the SWITCH statement duplicates records in a table visual.

Please, find Power BI file here:
https://drive.google.com/file/d/12dSYC-9U4yWfBlTYcR-8GY80aqvk5NhF/view?usp=sharing

0

StickClose

Sergio Murru

2 months ago

Reply to Evan Belkin

the problem is not the SWITCH, that works as expected.
The point is that the measure never returns BLANK()
Since the model you used is a snowflake and not a star schema, the SUMMARIZECOLUMNS in the query generated by Power BI cannot use auto-exists to only iterate on existing column combination, but does a cross-join of the product name and the subcategory, then evaluates all the measures and returns the rows for which non blank results are calculated.

Adding a check on existing rows matching the filter generated by the cross-join might help

<pre>
No Style =
IF (
  NOT ISEMPTY ( ‘Dim Product’ ),
  SWITCH (
    TRUE (),
    ISBLANK (
      MAX ( ‘Dim Product'[StyleID] )
    ), 1,
    0
  )
)
</pre>

these articles might help

https://www.sqlbi.com/articles/introducing-summarizecolumns/

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

1

Mounira SEKET

2 years ago

Hi Alberto,
First of all I would thank you for all the contents you share.
Now, I’m trying to use SWITCH function to return different results depending of an expression. here above an image of the non expected result.
Please what’s wrong with my code. Thank you a lot.

Switch Function_Error.PNG

0

StickClose

Sergio Murru

2 years ago

Reply to Mounira SEKET

23/01/2022 is not interpreted as a date but as 23 divided by 1 divided by 2022, that is a number less than zero. Also, to avoid issues due to date format used on the local machine, that could be mm/dd/yyyyy or dd/mm/yyyy I suggest to use the DATE funciton or to specify the literal of the date in the yyyy-mm-dd format, like for instance

<pre>
DATE( 2022, 1, 23 )

VALUE( “2022-01-23” )
<pre>

to convert a string to a date the VALUE fuction should be used

https://dax.guide/date/
https://dax.guide/value/

1

All

17:05

Comparing cumulative metrics for events with different start dates

Apr 23, 2024
26:39

Using EXPAND and COLLAPSE in visual calculations

Apr 9, 2024
25:49

Use scatterplots to find details in Power BI reports

Apr 3, 2024
01:39

Revolutionizing Power BI: Introducing the Ultimate Formula Language

Apr 1, 2024
19:52

Introducing EXPAND and COLLAPSE for visual calculations in Power BI

Mar 27, 2024
15:15

Vpax Obfuscator and DAX Optimizer – Unplugged #57

Mar 21, 2024
00:18

SqlBits 2024 – Day 1

Mar 20, 2024
00:35

Preparation for the training day @SqlBits 2024

Mar 20, 2024

Newsletter

By subscribing you are agreeing to our Privacy Policy.

227.1K subscribers

Get BI news and original content in your inbox every 2 weeks!
Newsletter archive

What is next

SQLBI Days – Introduction to DAX – Apr 30
, Chicago
SQLBI Days – Advanced DAX – May 1
, Chicago
SQLBI Days – First Steps in DAX Optimization – May 2
, Chicago
Mastering DAX – May 6
, Seattle
Mastering DAX – May 14
, Hamburg
Mastering DAX – May 22
, Copenhagen
SQLBI Days – Introduction to DAX – May 27
, Amsterdam

YouTube Twitter LinkedIn Facebook Github RSS Feed

Marco Russo Alberto Ferrari Daniele Perilli

2004-2024 © SQLBI. All rights are reserved.
SQLBI is a trademark of SQLBI Corp.
Loader is a European legal entity.

Privacy Policy & Cookies Terms & Conditions Contact Us

You are going to send email to

Move Comment