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
antmon99
Frequent Visitor

Slicer on one table to choose columns on another - Parameter table

Hi all,

 

I have one table called [ETO] that has a bunch of rows with columns like 

Job

Month

Value

Supervisor

Sales Consultant

Suburb

 

I would like to be able to have a single visual or table based upon sum(value), count(jobs) and month  and then using a slicer - dynamically add either the Supervisor, Sales Consultant or Suburb field into the visual

 

This idea of it works by creating a calculated column in my table like this

ColumnTest = if(CALCULATE(VALUES( ETO[Supervisor]),ETO[Supervisor]]= "John Smith") = "John Smith", ETO[Supervisor], ETO[Suburb])

 

and then creating a slicer on column ETO[Supervisor] and choosing 'John Smith' - it will change the value of ColumnTest

 

However I believe the slicer cannot be related to my table if I wish to get useful results, so I created a second table [Table1] with the values in a column called Chooser

Supervisor

Sales Consultant

Suburb

 

I then create a slicer on this table in my report

 

And in my [ETO] table I created a calc column similar to the above

DynamicColumn = if(CALCULATE(VALUES( Table1[Chooser]),Table1[Chooser] = "Supervisor") = "Supervisor", ETO[Supervisor], ETO[Suburb])

 

However the new ETO[DynamicColumn] only returns me names of Supervisors regardless of what the slicer Table1[Chooser] selection is

 

I was able to do this in a tableau report by creating a parameter with possible values and a dynamic column with nested case statements eg. NewColumn = case when parameter = 'Supervisor' then 'SupervisorColumn else 'SuburbColumn end

 

Is it possible to do something similar in Power BI

 

EDIT:  I got a step closer I think with this calculated measure

ParameterSelection = IF (    HASONEVALUE ( Parameter[ParameterValue] ),    VALUES ( Parameter[ParameterValue] ),"No Selection")

 

Though when creating a calc column  - TestColumn =  [ParameterValue]

All I get is 'No Selection" regardless of what I pick

Thanks

Anthony

1 ACCEPTED SOLUTION

Hi Baskar, cant seem to get that to work

 

I have it working using a parameter selection - see Case 2 from this link

http://biinsight.com/power-bi-desktop-query-parameters-part-1/

 

However every time the parameter is changed the report needs to refresh  - which is not how I want it done

View solution in original post

10 REPLIES 10
Beckham
Advocate II
Advocate II

I'd do a disconnected table/switch combination that looks like this:

 

Step #1 Create a disconnected table:

Number    Chooser   

0               Supervisor

1               Sales Consultant

2               Suburb

 

Step 2 the measure

= Switch(min(DisconnectedTable[Number]),

0,[Calculation #1],

1,[Calculation #2],

2,[Calculation #3])

 

Step 3. Bring the Chooser column on to the pivot as a slicer. When you select Suburb, the min will filter to 2, and the switch will run calc #3. 

 

Does this help?

Baskar
Resident Rockstar
Resident Rockstar

Hi Buddy

SWITCH(
if (HASONEVALUE('Measures Master'[Supervisor]),VALUES('Measures Master'[Supervisor]),"Non Selected"),
"XXX",SUM(Sheet1[XXX]),
"YYY",SUM(Sheet1[YYY]),
"ZZZ",SUM(Sheet1[ZZZ]))

Try this one. Let me know its working for you or not 🙂

Greg_Deckler
Super User
Super User

Can you explain this a little more? It may be that you are trying to exactly replicate something you did in Tableau when in Power BI you can achieve the same result a different way.

 

If you have if you have a visual or table with SUM([Value]), COUNT([Job]) and [Month] and you have you three slicers, [Supervisor], [Sales Consultant] and [Suburb], then choosing a slicer value from one of these three slicers will filter the results in your visual or table to just that selected value. I am unsure of the seemingly extra hoops you are jumping through. I am sure that there are good reasons, I just cannot understand those reasons yet from your post.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi smoupre

 

Perhaps if I explain how I did this in SSRS 🙂

 

Create a parameter with a bunch of available values

eg

Supervisor

Sales Consultant

Suburb

 

On a column in a table inside the report use an expression like

 

=iif(Parameters!Type.Label = "Supervisor", Fields!Supervisor.Value,

           iif(Parameters!Type.Label = "Suburb", Fields!Suburb.Value,

                iif(Parameters!Type.Label = "Sales Consultant", Fields!SalesConsultant.Value

                       ,  Fields!Company.Value)))

 

Effectively I would have a calculated coulmn that could be any of my other columns based upon what the slicer selection is

 

Thanks for you response

Anthony

 

 

Hello antmon99

 

Instead on multiple iff or case stmt , in power BI u can use Switch stmt. It will work for . Pls Check it

 

 

Hi Baskar,

 

I tried this

DynamicColumn = SWITCH(
if (HASONEVALUE(Parameter[ParameterValue]),VALUES(Parameter[ParameterValue] ),"Non Selected"), "Supervisor", ETO[Supervisor])

 

However regardless of what I choose on my slicer the column evaluates to blank

This is because the IF statement always evaluates to "Not Selected"

Slicer ValueSlicer Value

Calc Column on Slicer ValueCalc Column on Slicer Value

Obviously I am missing something here - very new to DAX

 

Thanks

Anthony

Hi Antmon, 

 u want to create new column in table ? 

 

if in page level please try this

 

SWITCH(CALCULATE(ALLSELECTED(Measure)),"Supervisor",ETO[Supervisor]).

 

 

 

Hi Baskar, cant seem to get that to work

 

I have it working using a parameter selection - see Case 2 from this link

http://biinsight.com/power-bi-desktop-query-parameters-part-1/

 

However every time the parameter is changed the report needs to refresh  - which is not how I want it done

Hi Baskar,

I'll give it a go now

Give me the pbix file i will help you

 

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.