Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

different outputs inside a calculated column using SWITCH()

hey,

 

im not new to powerbi or dax at all but im trying to achive someting and for the love of god im trying but always failing

 

the demand is:

 

i have a table:

 

(its distinct so we can see the data) 

 

so imagine i have 'Australia_Category' and i have 5 values for this attribute, and so on for the rest of the attributes

 

im trying to achive the following:

 

when a certain slicer is selected (region) i want to show the values based on the attributes on a matrix

 

so for example: 

 

lets say user choose USA on the region slicer 

 

 

i want to see all the attributes on a matrix (on rows) that belong to USA

 

if the user choose australia, i want to see australia values 

 

exp.

 

Column =

VAR SELECTED = SELECTEDVALUE('Organization Hierarchy'[Region])
RETURN
SWITCH(
TRUE(),
SELECTED = "USA", CALCULATE(SELECTEDVALUE('Item Category New'[Value]),'Item Category New'[Attribute] = "USA"),
SELECTED = "AUSTRALIA" , CALCULATE(SELECTEDVALUE('Item Category New'[Value]),'Item Category New'[Attribute] = "AUSTRALIA"))
 
but its always return blank() no matter what.
 
only if i use the CALCULATE(SELECTEDVALUE('Item Category New'[Value]),'Item Category New'[Attribute] = "USA") it return the expected results but its not dynamic by user slicer. 
 
 
if i try to use 
 
CALCULATE(SELECTEDVALUE('Item Category New'[Value]),FILTER('Item Category New', 'Item Category New'[Attribute] = SELECTEDVALUE('Organization Hierarchy'[Region]) ) it says circular dependecy error. 
 
im kind of lost, is it achivable? remeber that i need to put this on a matrix inside ROWS so i cant use a measure.. 
 
 
thank you very much

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can try measure like

    Measure =
    VAR _tab = allselected('Organization Hierarchy'[Region])
    RETURN
    CALCULATE(count('Item Category New'[Value]),filter('Item Category New', 'Item Category New'[Attribute] in _tab))

     

    you can plot

    'Item Category New'[Value]

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak 

    hey 

     

    thank you for your reply

     

    i dont need the count of the value, i need to actual values (hence why i used selectedvalue(value)) 

     

    when i use your syntax with selectedvalue it says circular dependecy 

     

     

    when i use it without the filter() i can make the measure without errors but i get all of the results regardless of what i choose in the slicer 

     

     

     

     

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    If you were to have a more column type structure to your table, it could help. Using transpose you could reshape the data. Something like:

    If all your rows were populated underneath the column  headers  similiar to this? Then it is much easier to set up dimension tables you want to filter on and for DAX to work easier.. Not sure if this is feasible, just a thought.