Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I have 2 tables in my model. Table1 containing a fact table with multiple columns State, City, Year, Month
Table1:
State | City | Year | Month | Temp |
FL | Tampa | 2010 | May | 89 |
NV | Las Vegas | 2015 | Jun | 100 |
WA | Seattle | 2020 | Apr | 70 |
I created a static calculated table which contains All the headers from table 1
Table2:
SNo | Value |
1 | State |
2 | City |
3 | Year |
4 | Month |
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 () ) )
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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:
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.
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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |