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

[AndreeaV]

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:

This is the slicer. It works for the graphs, but the "Selected options" measure should return 5.50, not 610.50. 

Here I post the measures to see what the results should be:

I posted the pbix sample file as a link, I don't know how to share in another way.

Sample file 

Thanks!

[JoeBarry · Accepted solution]

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

[AndreeaV]

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

[JoeBarry]

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.

[AndreeaV]

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.

[JoeBarry]

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 

[AndreeaV]

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?

[JoeBarry]

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]

@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.

[AndreeaV]

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

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