cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Avoiding Chained Lookup Tables

Hi,

 

I know it's best practice to avoid multiple levels of lookup tables. However, how is this generally done in practice? I have abbreviated city names coming from the fact table, some of which get consolidate (Baltimore into Washington, D.C., for example). I also want to sort these in situations where I don't want the fields in alpha order. Please see below:

 

Unique values from fact table on left, desired rollup on rightUnique values from fact table on left, desired rollup on right               I want to use this table as a slicer and have the ability to modify the sort orderI want to use this table as a slicer and have the ability to modify the sort order

Any way of doing this without creating two tables?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Avoiding Chained Lookup Tables

Hi @jl20

 

You can create a Sort calculated column in your FACT table if you want to avoid the extra table and then use this as your sort col for your main table

 

Sort = SWITCH(
        TRUE() ,
        -- IF/THEN ---
        'Table'[Office] = "Atlanta" , 1 ,
        'Table'[Office]= "Chicago" , 2 ,
        'Table'[Office] = "Cleveland" , 3 ,
-- ELSE -- 999 )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Avoiding Chained Lookup Tables

Hi @jl20

 

You can create a Sort calculated column in your FACT table if you want to avoid the extra table and then use this as your sort col for your main table

 

Sort = SWITCH(
        TRUE() ,
        -- IF/THEN ---
        'Table'[Office] = "Atlanta" , 1 ,
        'Table'[Office]= "Chicago" , 2 ,
        'Table'[Office] = "Cleveland" , 3 ,
-- ELSE -- 999 )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors