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

11 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

11 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

05:16

What happened in 2024

Dec 30, 2024
25:11

Reusing formatting for Power BI reports

Dec 17, 2024
07:01

Detect and Fix Issues with Tabular Editor 3 and DAX Optimizer Integration

Dec 9, 2024
40:08

BLANK in date columns and DAX time intelligence functions

Dec 3, 2024
08:07

Real-World Use Case for DAX Optimizer: Real Estate

Dec 2, 2024
30:25

Compare DAX Optimizer vs ChatGPT vs Copilot for Power BI

Nov 25, 2024
34:26

Computing open orders with visual calculations in DAX

Nov 19, 2024
03:48

Enhance Semantic Model Development with DAX Optimizer CLI (Command Line)

Nov 18, 2024

Newsletter

By subscribing you are agreeing to our Privacy Policy.

243.8K subscribers

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

What is next

Mastering DAX – Feb 11
, Amsterdam
Mastering DAX – Feb 18
, Phoenix
Mastering DAX – Feb 24
, Dallas
Data Modeling for Power BI – Apr 14
, Amsterdam

YouTube Twitter LinkedIn Facebook Github RSS Feed

Marco Russo Alberto Ferrari Daniele Perilli

2004-2025 © 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