Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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?
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 🙂
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.
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"
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |