Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help needed with DAX

Hello, 

Quick intro - 
I have few levels which look like Pyramid, (L1 1 value, L2 multiple values which goes into ever more values on L3 and so on until Level Node( which is level 8). 

I need a measure where when I select level 2 on slicer, level 3 will pop up on the visual(table). 
I have managed to do it until when level 5 is selected I get level 6 shown, but for 6 to 7 and 7 to node I cant get a correct measure. 
Commented is variable written in common langugae that should be translated to DAX langugae

Cost Centers Level Below FLag =
VAR vSelectedFilterLevel =
SWITCH(
TRUE,
ISFILTERED( 'cost center'[cost center name] ), 8,
ISFILTERED( 'cost center'[cost center level7 name] ), 7,
ISFILTERED( 'cost center'[cost center level6 name] ), 6,
ISFILTERED( 'cost center'[cost center level5 name] ), 5,
ISFILTERED( 'cost center'[cost center level4 name] ), 4,
ISFILTERED( 'cost center'[cost center level3 name] ), 3,
2
)
/* This part I cant get correct ! var level7 = If selected 2-7 return vSelectedFilterLevel, else return node*/

VAR vFilteredRecords =
FILTER(
'cost center list', -- if level 2 is selected in slicer return nodes from more detailed level, level3
'cost center list'[cost center (list) level] = "Level " & vSelectedFilterLevel + 1
&& 'cost center list'[cost center wid] IN VALUES( 'cost center'[cost center wid] )
)

RETURN -- just return 1 as flag if record should be visible
IF( SUMX( vFilteredRecords,1 ) > 0, 1, 0 )




DAX

2 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    Can you post sample data as text and expected output?
    Not enough information to go on;

    please see this post regarding How to Get Your Question Answered Quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.
    4. Relation between your tables

    Appreciate your Kudos!!
    LinkedIn:www.linkedin.com/in/vahid-dm/

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, 

    I need a measure which will return value if 2,3,4,5,6,7 from VAR vSelectedFilterLevel has been selected on the slicer, 
    Otherwise it needs to return column Node from table "ABC", 

    Thanks in advance,