Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following data model:
The purpose of the tables branched off of the 'Dimension Week' table are to allow the "holiday name" dimension in a table visual to be dynamic relative to a slicer selection. However, my ultimate goal is to not only make the dimension dynamic, but also to filter the 'Dimension Employee' / 'Fact Posted Labor' tables by the same slicer selection. How can this be done?
20200325 update:
Below is my data.
Fact Posted Labor
Employee Key | Week Key | Time (Hours) |
1 | 1 | 5 |
2 | 1 | 10 |
1 | 2 | 5 |
2 | 2 | 5 |
1 | 3 | 15 |
2 | 3 | 5 |
1 | 4 | 10 |
2 | 4 | 20 |
Dimension Week
Week Key | US Holiday | Jamaica Holiday |
1 | no holiday | no holiday |
2 | President's Day | no holiday |
3 | no holiday | Easter |
4 | Thanksgiving | Thanksgiving |
Dimension Employee
Employee Key | Employee Name | Holiday Type |
1 | John Doe | US Holiday |
2 | Jane Doe | Jamaica Holiday |
Holiday Sort
Holiday Type | Holiday | Sort Order |
US Holiday | President's Day | 1 |
US Holiday | no holiday | 2 |
US Holiday | Thanksgiving | 3 |
Jamaica Holiday | no holiday | 1 |
Jamaica Holiday | Easter | 2 |
Jamaica Holiday | Thanksgiving | 3 |
Holiday Type Slicer
Holiday Type |
US Holiday |
Jamaica Holiday |
bridge (an unpivot of Dimension Week)
Week Key | Holiday Type |
1 | US Holiday |
1 | Jamaica Holiday |
2 | US Holiday |
2 | Jamaica Holiday |
3 | US Holiday |
3 | Jamaica Holiday |
4 | US Holiday |
4 | Jamaica Holiday |
My measure is:
Total Time = sum of posted labor time = SUM('Fact Posted Labor'[Time (Hours)])
The values in the slicer will be the distinct [Holiday Type] values. These can come from either 'Dimension Employee' or 'Dimension Week' (since they're identical).
So, with a selection of "US Holiday" in the slicer, my expected output is:
Holiday | Total Time |
Presidents Day | 5 |
no holiday | 20 |
Thanksgiving | 10 |
obtained from:
[Employee Key] = 1 (i.e., the only US employee, as per 'Dimension Employee'):
[Holiday] is all distinct 'Dimension Week'[US Holiday] values:
"no holiday" (i.e., weeks 1 and 3) = 5 + 15 = 20 (sort order 2)
"President's Day"(i.e., week 2) = 5 (sort order 1)
"Thanksgiving"(i.e., week 4) = 10 (sort order 3)
And, with a selection of "Jamaica Holiday" my expected output is:
Holiday | Total Time |
Easter | 5 |
Thanksgiving | 20 |
no holiday | 15 |
obtained from:
[Employee Key] = 2 (i.e., the only Jamaica employee, as per 'Dimension Employee'):
[Holiday] is all distinct 'Dimension Week'[Jamaica Holiday] values:
"Easter" (i.e., week 3) = 5 (sort order 1)
"no holiday" (i.e., weeks 1 and 2) = 10 + 5 = 15 (sort order 3)
"Thanksgiving" (i.e., week 4) = 20 (sort order 2)
So far, my solution has involved putting a DAX column on table 'bridge' as:
FetchHoliday =
SWITCH(
FIRSTNONBLANK(
'Holiday Slicer'[Holiday]
,1
)
,"US Holiday", RELATED('Dimension Week'[US Holiday])
,"Jamaica Holiday", RELATED('Dimension Week'[Jamaica Holiday])
)
This allows [Holiday] in my table visual to be dynamic.
My slicer is from field 'Holiday Type Slicer'[Holiday Type]. My table visual has fields 'bridge'[FetchHoliday] (DAX dimension) and 'Fact Posted Labor'[Total Time] (measure). The dynamic values in [FetchHoliday] works as expected. However, [Total Time] is incorrect, because the slicer selection is not filtering 'Dimension Employee' / 'Fact Posted Labor' accordingly. And, the holiday values in the table visual are not sorted per the 'Holiday Sort' table.
Solved! Go to Solution.
One remark regarding the sort - it has to be consistent, i.e. 'no holiday' needs to have single number, not 2 numbers depending on holiday type. So as it is now you cannot really have it the way you want it (because in one scenario you show Thanksgiving after 'no holiday', and in the other it's before). You would need 2 separate sort orders for that.
Other option is to have 'no holiday' as first item (always), and then follow the holidays in calendar order, that should work as long as e.g. there are no 2 holidays with the same name that should have different order
I simplified the model to look like this:
in this setup the 'Dimension Week' is actually your bridge table (as they have identical contents), with original 'Dimension Week' not being loaded to the model. I also moved the sort here in a manner that would work, see here:
Week KeyHoliday TypeholidaySort Order
1 | US Holiday | no holiday | 0 |
1 | Jamaica Holiday | no holiday | 0 |
2 | Jamaica Holiday | no holiday | 0 |
3 | US Holiday | no holiday | 0 |
2 | US Holiday | President's Day | 1 |
3 | Jamaica Holiday | Easter | 2 |
4 | US Holiday | Thanksgiving | 3 |
4 | Jamaica Holiday | Thanksgiving | 3 |
Syntax for [Total Time]
Total Time =
CALCULATE (
SUM ( 'Fact Posted Labor'[Time (Hours)] ),
TREATAS ( VALUES ( 'Dimension Week'[Week Key] ), 'Fact Posted Labor'[Week Key] )
)
and the output
Hi @Anonymous ,
Would you please inform us more detailed information(such as your expected output and your sample data(by OneDrive for Business)) if possible? Then we will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Dedmon Dai
I just updated my post to include data, my expected output, and my solution attempt so far.
One remark regarding the sort - it has to be consistent, i.e. 'no holiday' needs to have single number, not 2 numbers depending on holiday type. So as it is now you cannot really have it the way you want it (because in one scenario you show Thanksgiving after 'no holiday', and in the other it's before). You would need 2 separate sort orders for that.
Other option is to have 'no holiday' as first item (always), and then follow the holidays in calendar order, that should work as long as e.g. there are no 2 holidays with the same name that should have different order
I simplified the model to look like this:
in this setup the 'Dimension Week' is actually your bridge table (as they have identical contents), with original 'Dimension Week' not being loaded to the model. I also moved the sort here in a manner that would work, see here:
Week KeyHoliday TypeholidaySort Order
1 | US Holiday | no holiday | 0 |
1 | Jamaica Holiday | no holiday | 0 |
2 | Jamaica Holiday | no holiday | 0 |
3 | US Holiday | no holiday | 0 |
2 | US Holiday | President's Day | 1 |
3 | Jamaica Holiday | Easter | 2 |
4 | US Holiday | Thanksgiving | 3 |
4 | Jamaica Holiday | Thanksgiving | 3 |
Syntax for [Total Time]
Total Time =
CALCULATE (
SUM ( 'Fact Posted Labor'[Time (Hours)] ),
TREATAS ( VALUES ( 'Dimension Week'[Week Key] ), 'Fact Posted Labor'[Week Key] )
)
and the output
Thank you for the attempt. My expected output does not include [Employee Key]. The table visual only includes [Holiday] and [Total Time]. When I restrict the table visual to only those 2 fields, all rows display an incorrect [Total Time] of 75. How can I resolve this to match the expected output shown in my post?
OK, I'm clearly missing some vital info here - based on what you said so far 75 seems to be correct here:
Total value for "US Holiday" is 5+20+10=35
Total value for "Jamaica Holiday" is 5+20+15=40
so with no filter specifying holiday type it's logical that the total value is 35+40 = 75. Employee Key is not really relevant here, I used it to relate to your examples better. It shows different values for employees because it relates 1:1 to Holiday Type, which is the one providing the split
What do you expect to see without specyfing the Holiday Type? What should be the value for that sum if not 75?
Yes, 75 is correct. However, the problem is that my desired table visual only has [Holiday] and [Total Time] -- it does not include [Employee Key].
For example, when [Holiday Type] = 'Jamaica Holiday' is selected in the slicer, I expect to see:
As you can see below, when [Employee Key] is not included in the table visual, all holidays incorrectly return [Total Time] = 75.
in the slicer you need to use Holiday Type from 'Dimension Employee', as it filters the 'Dimension Week', not the other way round
in general I would use TREATAS (more here https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/)
If you need more specific help, then can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 | Column2 |
A | 1 |
B | 2.5 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |