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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
AndreeaV
Advocate I
Advocate I

SWITCH based on measures doesn't work, but the individual measures work as expected

Hi,

 

I created this SWITCH measure. It is based on a slicer, so if the user presses "1W", it should give the value of the [6M last week change] measure, if the user presses "1M", it should give the value of the [6M last month change] measure etc. 

 

The issue is that although the measures work as expected by themselves, when I place them in the SWITCH-based measure, the results are far from what I expect.

 
Selected options =
SWITCH(
   SELECTEDVALUE(DateRangePeriod[Index]),
    1[6M last week change],
    2[6M last month change],
    3[6M last quarter change],
    4[6M last semester change],
    5[6M last year change],
    6[6M last 5year change],
    7, "")
 
This is the model:
 
DAX model.png
 
This is the slicer. It works for the graphs, but the "Selected options" measure should return 5.50, not 610.50. 
slicer.png
Here I post the measures to see what the results should be:
measures work fine.png
I posted the pbix sample file as a link, I don't know how to share in another way.
 

Thanks!

1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

I approach Switch in a slightly different way.

Remove the relationship DateRangePeriod and Date. Make Sure that in the DateRangePeriod table that Days Range is sorted by Index. Create a measure 

Selected = MIN(DateRangePeriod[Index])

 

Change the measure to below

Selected options =
SWITCH(
   [Selected],
    1, [6M last week change],
    2, [6M last month change],
    3, [6M last quarter change],
    4, [6M last semester change],
    5, [6M last year change],
    6, [6M last 5year change],
    7, "")

 

The date from the Date table should be used in the visual and change the relationship between the Date and Graph table to one to many. The date table should also be marked as a date table https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

 

Cheers

Joe

View solution in original post

9 REPLIES 9
JoeBarry
Solution Sage
Solution Sage

I approach Switch in a slightly different way.

Remove the relationship DateRangePeriod and Date. Make Sure that in the DateRangePeriod table that Days Range is sorted by Index. Create a measure 

Selected = MIN(DateRangePeriod[Index])

 

Change the measure to below

Selected options =
SWITCH(
   [Selected],
    1, [6M last week change],
    2, [6M last month change],
    3, [6M last quarter change],
    4, [6M last semester change],
    5, [6M last year change],
    6, [6M last 5year change],
    7, "")

 

The date from the Date table should be used in the visual and change the relationship between the Date and Graph table to one to many. The date table should also be marked as a date table https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

 

Cheers

Joe

Hi Joe,

 

Thanks for the solution, it worked. How did you detect the DAX relationship problem?

 

I would like to understand DAX relationships, that's what I am missing. I know what one-to-one, one-to-many etc. mean, but I don't know who filters what in models when I see them in Model View.

 

I can work with DAX, I know around 20-30 functions, but I don't understand relationships very well. 

 

Did you learn DAX from a book or a course? 

 

Cheers,

Andreea

 

Hi @AndreeaV 

 

The first thing that jumped out was the bi directional relationships, if possible, the relationships bewteen tables should be one to many. Having a BI Directional allows for filtering both ways and this can give false results in some cases.

 

The table DateRangePeriod for this case didn't need a relationship. 

 

You can start here for a overview, read all the articles below also https://learn.microsoft.com/en-us/power-bi/guidance/star-schema The Star Schema optimises the performance of DAX in Power BI. 

 

There are many books I have read, this one for example is great https://www.amazon.de/gp/product/B09R4STWGJ/ref=kinw_myk_ro_title this is a best prcatices guide which covers Relationships also.

 

For Dax, I would recommend @marcorusso  and @AlbertoFerrari's  https://www.amazon.de/gp/product/B07TS4FPZM/ref=kinw_myk_ro_title 

 

There is also a ton of other free information out there.

 

 

 

Hi Joe,

 

I know what star schema, fact table and dimension tables are, but who filters the fact table? The primary key in every dimension table or all the columns in every dimension table? 

 

I completed the Microsoft Learning training and around 10 other PowerBI courses, but none of them showed this explicitly.

 

Thank you for your time and very useful info.

 

 

 

 

Each dimension table (lets call it DIM Product) as you know has a unique primary key (Lets call it ProductID). this table will also have a ProductDescription column .

 

The Fact Table will have a column with this ProductID also and this column isn't unique. You then create a relationship between these two columns. In this case a one(DIM Product) to many (Fact) relationship.

 

When this created, when you are creating a visual or a slicer and you need the ProductDescription, you use the column from the DIM Product table. So the Dimension table is filtering the Fact table. 

 

The arrow will show you what direction the filtering is happening 

JoeBarry_0-1692614600576.png

 

 

 

 

OK, so the primary key in the dimension table filters the fact table (actually, a foreign key in the fact table).

 

And when the relationship is bidirectional, it means that the primary key in the dimension table filters the fact table and the primary key in the fact table filters the dimension table?

Hi @AndreeaV 

 

This is exactly what will happen. 

 

Test it out. Create two table visuals one with data from the dimension and the other from the fact table. Click on the values in each table and test. Then change the relationship types to see the impact each has.

 

Thanks

Joe

amitchandak
Super User
Super User

@AndreeaV , I have not seen the measures, but make sure the date used in the measure and the date used on the axis are coming from same date table

 

 

If this does not help
Can you share a sample pbix after removing sensitive data.

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi. I shared a link to the sample file in this post. 

 

Only superusers can share files directly. Congrats for being a superuser 👏👏👏

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.