how to filter unrelated table

[Anonymous]

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.

[v-deddai1-msft]

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

[Anonymous]

@v-deddai1-msft  and @Stachu 

I just updated my post to include data, my expected output, and my solution attempt so far.

[Stachu · Accepted 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

[Anonymous]

@Stachu 

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?

[Stachu]

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?

[Anonymous]

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.

[Stachu]

in the slicer you need to use Holiday Type from 'Dimension Employee', as it filters the 'Dimension Week', not the other way round

[Stachu]

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