Switch Statement - only first line working

[knowledgegarage]

Hi,

I have an issue with a switch statement.

Basically, I am trying to use the switch as a way of changing the calculation of the column that is storing the values.

In the dataset there is a column called 'attribute' and a column called 'values'. There is also an attribute table linked to the 'attribute' field that will tell you the 'Aggregation'.

When this aggregation is e.g. 'Count' I want it to use the Count (Distinct) measure.

So far it works for whatever the first line is but not the other lines; so I assumed by code was wrong.

Any ideas?

Selection of Measure =

SWITCH(TRUE(),

"Allocation" IN ALLSELECTED(Attribute_Table[Aggregation]), [Allocation Calculation],

"Count" IN ALLSELECTED(Attribute_Table[Aggregation]), [Count (Distinct)],

"Weighted Average" IN ALLSELECTED(Attribute_Table[Aggregation]), [Weighted Average %],

"Allocation (per million)" IN ALLSELECTED(Attribute_Table[Aggregation]), [Allocation per m],

"Percentage Sum" IN ALLSELECTED(Attribute_Table[Aggregation]), [Percentage SUM Positive])

[v-yalanwu-msft]

Hi, @knowledgegarage ;

You could change it.

Selection of Measure =
SWITCH (
    SELECTEDVALUE ( Attribute_Table[Aggregation] ),
    "Allocation", [Allocation Calculation],
    "Count", [Count (Distinct)],
    "Weighted Average", FORMAT ( [Weighted Average %], "0.00%" ),
    "Allocation (per million)", [Allocation per m],
    "Percentage Sum", [Percentage SUM Positive]
)

Here i test a simple example.

Selection of Measure = 
SWITCH(SELECTEDVALUE(Attribute_Table[Aggregation]),
"Count" , [Count (Distinct)],
"Weighted Average" ,FORMAT( [Weighted Average %],"0.00%"))

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33 · Accepted solution]

Hi @knowledgegarage 

My guess is you want this

Selection of Measure =
SWITCH(SELECTEDVALUE(Attribute_Table[Aggregation]),
"Allocation" , [Allocation Calculation],
"Count" , [Count (Distinct)],
"Weighted Average" , [Weighted Average %],
"Allocation (per million)" , [Allocation per m],
"Percentage Sum" , [Percentage SUM Positive])

[knowledgegarage]

Fantastic.

Second question, as some of these are % and the others are numerical is there a way of forcing say 'Count Distinct' to be numerical and 'Percentage Sum' to be %?