CODEWITHSUNDEEP
powerbidax
Nomad82
Nomad82

Reputation: 7

Is it possible to use values from a disconnected table to filter selection in a slicer in DAX?

I have a disconnected table that have a column with two Values FisYear and CalYear.

I also have a calendar table with dates and two columns, one FisYear and the second CalYear. The FisYear have values FY2021, FY2122 and FY2223. The CalYear column have values 2020, 2021, 2022 and 2023.

The calendar table is connect to the fact table.

I want to have two single value filter visuals, one based on the disconnected table and the other on the calendar table.

The functionality I'm after is when I choose say FisYear, the calendar table would only show the available selections from the FisYear column and similarly for CalYear.

I tried using the SWITCH statement but it's returning and error as it should only return a scalar value and not a list.

Thanks for the help!

Upvotes: 0

Views: 841

Answers (2)

Peter
Peter

Reputation: 12345

This is what Field Parameters are made for:

enter image description here

enter image description here

enter image description here

Upvotes: 1

Marcus
Marcus

Reputation: 4005

Ok, I think I understand. I think you need the following:

  1. A new table persisted to your data model that contains the combinations of all FisYear years and corresponding dates and all CalYear years and corresponding dates.
  2. New relationships set up between your selector table, the new "combinations" table and between your new "combinations" table and your existing date table.

The new table would look like this:

Combinations = 
VAR _fisYear = ADDCOLUMNS ( 
    SUMMARIZE ( 
        'Date' , 
        'Date'[Date] , 
        "YearValue" , SELECTEDVALUE ( 'Date'[Fiscal Year] ) 
    ), 
    "Selection", "FisYear"
)

VAR _calYear = 
 ADDCOLUMNS ( 
    SUMMARIZE ( 
        'Date' , 
        'Date'[Date] , 
        "YearValue" , SELECTEDVALUE ( 'Date'[Calendar Year] ) 
    ), 
    "Selection", "CalYear"
)

RETURN 
UNION ( _fisYear , _calYear )

You can relate this table to your selection table on the Selection column. You can relate this table to your existing date table on the Date column. When you create the relationship to your date table, specify a many-to-many relationship, and that this new Combinations table filters date (and NOT the other way around!).

You should now be able to use the YearValue column in a slicer which gives you all the different combinations of fiscal years and calendar years options. When selecting FisYear or CalYear in a slicer you should only see the corresponding values for YearValue.

Upvotes: 0

Related Questions