Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Thanks!
Solved! Go to 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
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
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
@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.
Hi. I shared a link to the sample file in this post.
Only superusers can share files directly. Congrats for being a superuser 👏👏👏
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |