how to dynamically change dimension field in table visual with a sort

[Anonymous]

I want to dynamically change a dimension field in a table visual according to a slicer selection.  I know that this has 2 usual solutions, as described here:

https://www.thedataschool.com.au/danica-hui/switching-dimensions-in-power-bi/

However, in my case, I also want:

1) the dimension field values to be custom sorted

2) the slicer selection to drive the filtering of another dimension table

I have the following 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	Presidents 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	Holiday Sort Order
US Holiday	Presidents Day	1
US Holiday	no holiday	2
US Holiday	Thanksgiving	3
Jamaica Holiday	Easter	1
Jamaica Holiday	Thanksgiving	2
Jamaica Holiday	no holiday	3

My measure is:

Total Time = sum of posted labor time = SUM('Fact Posted Labor'[Time (Hours)])

I want a table visual of Holiday and Total Time, where the Holiday values will be dynamic relative to a slicer selection and where the holidays are sorted per the 'Holiday Sort' table above.  The values in the slicer will be the distinct [Holiday Type] values in 'Dimension Employee' (which is identical to the two "holiday" columns in 'Dimension Week').  Again, not only does the selection of values need to determine what holidays appear in the table visual, but it must also filter the 'Fact Posted Labor' table as appropriate relative to the 'Dimension Employee' table.

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)

        "Presidents Day"(i.e., week 2) = 5                           (sort order 1)

        "hanksgiving"(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)

How can I achieve this?  A bookmark solution is not permissible.

[camargos88]

Hi @Anonymous ,

I don't think it's possible to order a column when the column values are duplicated, I think PBI doesn't allow it because of context filter.

However, I tried another solution that can work for you.

You can add the sort order column to your table, sort the table by this column and hide the column (just drag the column till you hide it).

It's gonna work only for table/matrix.

I will continue thinking about a better solution for it.

Ricardo

[Anonymous]

@camargos88 

Thank you for looking into it.

By the way, I have created a solution for toggling the dimension field in my table.  The solution I am using is to create 2 new tables branched off of the Dimension Week table.  The first one is a "slicer" table.  Its structure is:

Holiday Slicer

Holiday

US Holiday

Jamaica Holiday

The second one is a "joiner" table.  Its structure is:

bridge

Week Key	Holiday
1	US Holiday
1	Jamaica Holiday
2	US Holiday
2	Jamaica Holiday
3	US Holiday
3	Jamaica Holiday
4	US Holiday
4	Jamaica Holiday

I then put a DAX column onto the bridge table as:

FetchHoliday =

SWITCH(

    FIRSTNONBLANK(

        'Holiday Slicer'[Holiday]

        ,1

    )

    ,"US Holiday", RELATED('Dimension Week'[US Holiday])

    ,"Jamaica Holiday", RELATED('Dimension Week'[Jamaica Holiday])

)

I then wire up the tables with many-to-many relationships as follows:

Lastly, I create a slicer from 'Holiday Slicer'[Holiday] and a table visual from 'bridge'[FetchHoliday] with measure [Total Time], as follows:

[Anonymous]

My solution does not solve the problem of filtering the Dimension Employee table to the correct rows.  A work-around I have is to have the user make the same selection in 2 slicers -- one that drives which holidays appear in the table visual, and another one that drives the Dimension Employee filtering.  Obviously, this isn't ideal.  So, I need a solution that resolves this problem as well as the sorting problem.