Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic filter showing column values based on another filter

Hello All,

I have 2 tables in my model. Table1 containing a fact table with multiple columns State, City, Year, Month

Table1:

StateCityYearMonthTemp
FLTampa2010May89
NVLas Vegas2015Jun100
WASeattle2020Apr70

 

I created a static calculated table which contains All the headers from table 1

Table2:

SNoValue
1State
2City
3Year
4Month

 

I am trying to have 2 drop downs where drodown 1 has values from table 2.

the 2nd dropdown will have values that will dynamically change based based on the selection of filter 1.

For example- If State is selected from first drop down would like to see All states in the 2nd slier.

If City is selected all cities shpuld be displayed.

I am able to achive the above scenario only for measures (like Avg(temp)) but not columns itself. Any idea if we can achive the reult for columns to get their distinct values?

 

The below measure gives error A table of multiple values are supplied when a single value is expected.

 

Measure_dynamic=
IF (
    HASONEVALUE ( 'Table2'[S.No] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Table2'[S.No] ) = "1", 'Table1'[State],
        VALUES ( 'Table2'[S.No] ) = "2", 'Table1'[City],
        VALUES ( 'Table2'[S.No] ) = "3", 'Table1'[Year],
        BLANK ()
    )
)
2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

IF/SWITCH will not return tables, so the naked column references in your returns give an error.  One way to accomplish your goal is to just have 4 slicers for your 4 columns (city,state, year month).

 

Another way to consider is to unpivot your data (in your current or in an additional table).  Then you will have one column that has your Slicer 1 values and another with your Slicer2 values.  In your measure, you can just require that Slicer 1 hasonevalue to make sure you are alway getting a meaningful number (that you can slicer further with Slicer2.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Anonymous
Not applicable

as @mahoneypat mentioned, only piviting worked. 

 

In my table had millions of rows and pivoting is not ideal.

 

So, I ended up creating a static table just for the drop down. In the latest power bI release, we can have parametrzed page navigation. So eneded up creating a measure and eneblaed page navigation based on the filter selection.

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

If you want measure to show dynamic values based on slicers, you can create measure like this and put it in a table visual, but it also needs a fact column as a reference and you cannot put measures as slicers in power bi desktop.

Measure =
IF (
    ISFILTERED ( Table2[Value] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Table2'[SNo] ) = 1, SELECTEDVALUE ( Table1[State] ),
        VALUES ( 'Table2'[SNo] ) = 2, SELECTEDVALUE ( Table1[City] ),
        VALUES ( 'Table2'[SNo] ) = 3, SELECTEDVALUE ( Table1[Year] ),
        VALUES ( 'Table2'[SNo] ) = 4, SELECTEDVALUE ( Table1[Month] ),
        BLANK ()
    ),
    "NULL"
)

Use the [temp] column as a reference:

dynamic values.pngdynamic values2.png

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

as @mahoneypat mentioned, only piviting worked. 

 

In my table had millions of rows and pivoting is not ideal.

 

So, I ended up creating a static table just for the drop down. In the latest power bI release, we can have parametrzed page navigation. So eneded up creating a measure and eneblaed page navigation based on the filter selection.

Anonymous
Not applicable

With the first approach,in my original dashboard,  I have to display a total of 15 slicers which occupies a lot of realestate. That is why wanted to explore another option. Guess this is the only option then.

 

I tried the second approach a couple of days back. My original data set has arounbg 7M rows. By unpivoting with all the values needed, the number is going upwards of 100M rows. This is taking a big hit to my model. The performance was not satisfactory. So trying to avoid this route.

 

With the first approach, I am going to hide/uhide the slicer panel using bookmarks to save some space in the dashboard then.

 

Thanks for clarifying.

 

Dusi

mahoneypat
Employee
Employee

IF/SWITCH will not return tables, so the naked column references in your returns give an error.  One way to accomplish your goal is to just have 4 slicers for your 4 columns (city,state, year month).

 

Another way to consider is to unpivot your data (in your current or in an additional table).  Then you will have one column that has your Slicer 1 values and another with your Slicer2 values.  In your measure, you can just require that Slicer 1 hasonevalue to make sure you are alway getting a meaningful number (that you can slicer further with Slicer2.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.