Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer Parameter Tabl

No video

Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer Parameter Tabl

Рет қаралды 54,250

Күн бұрын

The trick I am about to explain in this video is used in many samples, and it is not a new trick. However, it is very useful, and many Power BI users are still not aware of it. Let’s say you have to columns: Cost, and Sales, and in one chart, you want to show only one of the measures above, but the selection to be done in a slicer. You want the user to select either the cost or sales and then see that value in the visual. Another example is that you have all the time intelligence calculations as measures, and you want to have a slicer to select what to show as the value in the chart. In this video, I’ll explain how you can do that.
Download the Power BI file from my article below:
radacad.com/ch...
*******************
Want to learn about Power BI or AI? or take your Power BI and AI knowledge to the next level for building reports? Check out our courses in RADACAD Academy for all aspects of Power BI and AI.
RADACAD courses: learn.radacad.com
Become an academy member: learn.radacad....
*******************
********
CONNECT with US!
********
-- / radacad_com
-- / rad_reza
-- / leila_etaati
-- / radacad
-- / leila-etaati-b0304636
-- / rezarad
-- / 6393399
-- radacad.com
********

Пікірлер: 59

@11tushaar 2 жыл бұрын

Used to do column swap in tableau using parameters. Was looking for this

@AbhayGadiya 3 жыл бұрын

How the column title in matrix visual can also change based on selection

@brief001 3 жыл бұрын

What a good explanation, I now also use this trick. one question: Is it possible that the formating can differ per measurement. For example, I have currency as a measurement, but with other measurements it must be in percentages? Is there a solution for that?

@sati029 3 жыл бұрын

This is very helpful. Solved a problem that i have been hitting my head with. However, is it possible to implement this for multiple selection in the slicer? Can we switch among columns without creating aggregated measures, and switching among those measures? thanks

@alimkumykov1277 4 жыл бұрын

This doesn't work with columns, only with measures. I get following error with [Sales All Measures] DAX - "A single value for column 'Y' in table 'X' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

@jamespaz4333 11 ай бұрын

Great tutorial! What would you recommend if we have 3 slicers interacting? probably creating an specific DAX formula for each combination might be a pain on the neck... thank you!

@daveb5374 2 жыл бұрын

Brilliant , best explanation Ive come across

@RADACAD 2 жыл бұрын

Wow, thanks!

@welcometomathy 2 жыл бұрын

just what I wanted

@RADACAD 2 жыл бұрын

Glad to help!

@mereskhdigi9938 4 жыл бұрын

Nice tutorial. One question, how can you select more values? Like in the Switch: 1,[(XYZ)A] && [(XYZ)B], ? Could you help me plz?

@anthonysweet812 3 жыл бұрын

Hi there, nice video! How can I reference columns, rather than measures? The fields I need to use cannot be aggregated.

@cansokullu6704 3 жыл бұрын

thats what i came here for as well. name of the video is misleading

@RADACAD 3 жыл бұрын

can you share your sample datasets with me?

@bhawanakant7595 3 жыл бұрын

@RADACAD can we do the same thing with columns/dimensions instead of measures. Example, the way you are switching view by measures (cost, profit etc), can we do the same thing and switch dimensions (country, zone, region etc) instead of measures.

@summer_xo 2 жыл бұрын

@@cansokullu6704 agreed, disappointing.

@Kmsbi 4 жыл бұрын

Thanks Reza for giving us useful information.

@mahrmahr9871 2 жыл бұрын

Thank you I was looking for this

@aryansena7290 3 жыл бұрын

Hi , Can you create a table as per slicer selection .. consider I'd column in a rows like 1,2,3,4. And a slicer using this I'd column.. now if I select I'd from slicer 3 then a table will be created which has all data with I'd 3. Please let me know if requirement is not clear

@SAREL2385 3 жыл бұрын

Helped me big time!! You made my day. God bless you!

@RachelMasonGinn-f6q 19 күн бұрын

Thank you!!

@RADACAD 17 күн бұрын

You're welcome!

@rajdivate8186 3 жыл бұрын

Hi Reza, is there a way to change the measure name dynamically . Like in your video when you select Year to date or Quarter to date the measure name should change accordingly instead of " All Measures". I understood how to change the title dynamically, can the measure name also be changed dynamically?

@JuniorDIEKA 3 жыл бұрын

Hello Radcad! thank you for this but I made use of your technics and creating a dynamic column with DAX measure but when I am putting it on the axis within the bar chart it only shows the first selection even if I select something else, Kind assist by providing more details on how to do it. Thank for your support and time.

@himanshupal3439 3 жыл бұрын

Hi there, it's very good & illustrative video for this topic. Keep it up! Gentleman

@saisatya5525 4 жыл бұрын

Good video , I have a question instend of measure can I keep calculated column in measure?

@dineshsainani6296 3 жыл бұрын

Hey Sai, did you get any reply on this?

@nasimmiremad4659 3 жыл бұрын

Thanks a lot for such an incredible knowledge sharing!

@nitiknijhawan4187 4 жыл бұрын

Hi Reza, when I am downloading and opened this report in my system ,it is saying invalid or corrupt file.Can you please guide.

@NorwayAerials 3 жыл бұрын

I discovered a challenge with this method. Say I would like to selected between two measures, which I would like to display in different number formats. Measure 1: Gross profit i thousands. Measure 2: Gross profit in % of revenue. Since we use only one measure ("All measures" in you video) in the visual, it can only be formatted in one format (say thousands for example). Do you see any way to resolve this?

@aminebouhouche1804 3 жыл бұрын

Thanks for the video. I would like to have the legend (column series) to be selected from a dropdown using the same logic you used. Any suggestions how to approach it?

@brief001 3 жыл бұрын

I have the same question. Have you found the solution?

@javierar87 3 жыл бұрын

Me too!! Same question here! Thanks!

@jennytumay 2 жыл бұрын

I created a video solving this problem on my channel “Dynamic Columns Based on Slicer Selection in Power Bi” 😁

@thaiscristinasouza3427 2 жыл бұрын

Thank you so much for the amazing content.

@NorwayAerials 3 жыл бұрын

Great and very useful video - thanks a lot!

@Bharath_PBI 3 жыл бұрын

Thank you, if I want to take it further, for example, I've a drill through details page, from this technique, the drill through to details tab, won't work as it doesn't pass the filter. Any suggestions on this?

@amiryazdani9723 2 жыл бұрын

This is fantastic, thank you so much. How do I do this for switching between dimensions? Example would be picking between "City" or "County" , I can not create a measure for these since there is no aggregation .

@jfelipevb 2 жыл бұрын

To the point. Thank you sir.

@rodrigomachado3678 3 жыл бұрын

Thanks! This is quality material. Thanks!

@laurapotonnier3464 2 жыл бұрын

Thanks for the very useful video ! What if I have 180 different measures ? Basically I did this : SWITCH(MeasuresTable[SelectedMeasure], "A1" , sum(DataTable[A1]), ....... , "A180" , sum(DataTable[A180])) which works fine but it is very long (all indicators from A2 to A179 are implied with the dots inbetween). Isn't there a way to use some "indirect" function like excel where I can retreive the value from the column which name is the same as my selected value? My goal is to make a very flexible report... Thanks again!

@kimmend83 3 жыл бұрын

Super video, just what i needed.

@TrekTales7595 4 жыл бұрын

Can it possible to change the y-axis title based on measure value

@seshakv 3 жыл бұрын

Thank you so much

@valentinloghin4004 3 жыл бұрын

Thank you very much Reza for sharing this to us , I wondering how can we do the same for the axis ?

@RADACAD 3 жыл бұрын

if you mean changing the title of the X-axis, that is not possible with expression yet. workaround is to use a card visual lay over on it. if you mean something else, please explain more.

@vijayvizzu1 4 жыл бұрын

Nice trick, but can we apply the same technique for multiple selection in the slicer ?

@RADACAD 4 жыл бұрын

depends on what you want to do. for example, selecting YTD and QTD at the same time doesn't make sense. However, if you have something that multiple selection makes sense, then possible. instead of SELECTEDVALUE, you can use SUM of the code column, but you should change the code numbers that SUM of some codes, won't be similar to another code, for example multipliers of 2 as a code.

@totvabe1 4 жыл бұрын

Fantastic!

@sathishkumar_bhutharaju 4 жыл бұрын

Hi Reza,hope you doing well... I have stucked at mid of my project that's how to use SQL Store Procedure in Direct Query or Live Connection mode... can u pls help me on this or give any idea for how to use Store Procedure's in Live connections. thanks in advance 😊😊😊😊

@RADACAD 4 жыл бұрын

Live Connection is only to SSAS, AAS, or Power BI. You can't have Stored procedure on that. But for DirectQuery. Just write your "exec stored proc" in the advanced editor section of Get data from SQL Server

@sathishkumar_bhutharaju 4 жыл бұрын

@@RADACAD I tried with Import mode it's working,but Direct Query it is not working pls give me any suggestion or please do one video regarding this

@kembawxlker Жыл бұрын

this method doesn't work on columns.

@sau002 3 жыл бұрын

Excellent. Why so few upvotes?

@adityadeshmukh3150 4 жыл бұрын

♥ it....! Very useful, thanks. 😊👍

@JasonWeitzel 3 жыл бұрын

I tried following but the explanations are all over the place. You bounce around so much I have no idea how the measure you're speaking to relates to the visual if at all.

@RachelMasonGinn-f6q 19 күн бұрын

Thank you!!

@RADACAD 17 күн бұрын

Welcome!