Is it possible to display the second level of hierarchy data while selecting only one slicer option?

[vignesvar]

Dear team, 

I would like to design a grid report, which should display the second level of details while selecting only one primary level data in the slicer. If I selected multiple options in the primary level slicer then the grid should display the selected primary level data. Is it possible to achieve this on a Power BI desktop?

I have a single table with a proper hierarchy level as well. I have tried the DAX function using IF() but the true/false section table attribute is not displayed for the suggestion. it is a text data type. Any Idea that will be helpful to achieve this design? 

[v-tianyich-msft]

Hi @vignesvar ,

I've made a number of attempts and it's not quite clear what you're intending to do, but you can check if the following results meet your expectations:

Measure = 
IF (
    HASONEVALUE ( Table[Primary Level] ),
    SELECTEDVALUE ( Table[Second Level] ),
    CONCATENATEX ( VALUES ( Table[Primary Level] ), Table[Primary Level], ", " )
)

Please feel free to correct me and provide more information if I have misunderstood you!

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

[vignesvar]

Hi @v-tianyich-msft, 

Thanks for your response. The result table has its attributes and measures. There are two slicers each have its own level level 1 and level 2. 

If the user selected one value from the level 1 filter, then the table should display the level 2 related to the selected level 1 with its count. If more than one value is selected for the level 1 filter, then the selected level 1 attributes and their summary count. Please find the image below for your reference. 

I have tried the below DAX measure to achieve the expected results but data is not display on the given column. 

LevelCounter = COUNTROWS(VALUES('Table 2'[l1]))

FirstLevel = IF([LevelCounter]>1, SELECTEDVALUE('Table 2'[l1]), BLANK())

SecondLevel = IF([LevelCounter]>1, SELECTEDVALUE('Table 2'[l2]), BLANK())

DynamicCol = IF(NOT ISBLANK([FirstLevel]), [FirstLevel], [SecondLevel])

Sample result screen for your reference below.

Regards,

Vignesvar

[v-tianyich-msft]

Hi @vignesvar ,

I'm not quite sure I understand your needs.Could you provide a sample pbix or a detailed reproduction step? (Please be careful not to include sensitive information)

Best regards,
Community Support Team_ Scott Chang

[vignesvar]

Hi @v-tianyich-msft ,

Thanks for your response. Please find the PBIX file for your reference. It is a sample file only, with no sensitive information. I request you to go through Page 5 for the design. 

https://drive.google.com/file/d/1I-w2F0tcaefJ5gKYtHOzLPiD_gJI8Gfw/view?usp=sharing

Regards,

Vignesvar

[v-tianyich-msft]

Hi @vignesvar ,

I made some modifications to your original expression and the result is as follows:

FirstLevel = IF([LevelCounter]=1, CONCATENATEX('Table 2','Table 2'[Seg3_L2],","), IF([LevelCounter]>=2,CONCATENATEX(SUMMARIZE('Table 2','Table 2'[Sname]),'Table 2'[Sname],","),BLANK()))

LevelCounter = DISTINCTCOUNT('Table 2'[Sname])

SecondLevel = IF([L2 Filtered]=TRUE(), CONCATENATEX('Table 2','Table 2'[Sname],","), BLANK())

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

[vignesvar]

Hi @v-tianyich-msft , 

It looks cool and thank you very much for your assistance.

Is it possible to display the results in tabular format?

Regards,

Vignesvar

[v-tianyich-msft]

Hi @vignesvar

It seems to be unsupported for now.

Best regards,
Community Support Team_ Scott Chang

[vignesvar]

Hi @v-tianyich-msft , 

Thank you very much for your support and response.

Regards,

Vignesvar