Display selected value from hierarchy slicer in Power BI

Question

Good Morning,

I'm working on building a custom function/slicer that would display the values for a selected values in the filter. There is Hierarchy within these tables.

The problem is when I select a particular values in a lower hierarchy it correctly shows me the value, but when I select a higher hierarchy it shows the values for lower hierarchy along with the values for higher hierarchy as well. I only want to display the values for Higher hierarchy when it is selected.

The hierarchy looks like lowest is facility then market then group and region is the highest. The values corresponding to each is not related but the hierarchy is there.

My code looks like this in DAX

Selectvalue_custom = 
VAR Fac = SELECTEDVALUE('Hierarchy'[Facility])
VAR Mar = SELECTEDVALUE('Hierarchy'[Market])

VAR Facility_result = SELECTCOLUMNS(FILTER(Facility, Facility[Facility] = Fac), "Facility", Facility[Points])
VAR Market_result = SELECTCOLUMNS(FILTER(Market, Market[Market] = Mar), "Market", Market[Points])

RETURN 
    IF (
        AND((ISBLANK(Fac)), (ISBLANK(Mar))),
        BLANK(),
        IF (
            NOT(ISBLANK(Fac)),
            Facility_result,
            IF (
                NOT(ISBLANK(Mar)),
                Market_result,
                BLANK()
            )
        )
    )

Answer 1

Hello Ayush,

Thank you for your question and for reaching out with your question today.

Based on your description and code, it seems like you want to build a custom DAX function to display the values for the selected hierarchy level while avoiding displaying lower-level values when a higher hierarchy level is selected.

In your DAX code, you are using the `SELECTEDVALUE` function to get the selected values for Facility and Market hierarchies, and then you are filtering the corresponding tables (Facility and Market) based on those selected values.

The issue you are facing is that when you select a higher hierarchy level (e.g., Market or Group), the function also returns values from the lower hierarchy (Facility or Market), which is not what you want.

To address this, you can modify your DAX code to use `HASONEVALUE` function, which will help you determine whether a higher-level hierarchy has been selected. If a higher-level hierarchy is selected, you can display the values for that level only, and if a lower-level hierarchy is selected, you can display the values for that level.

Here's an updated version of your DAX code:

```DAX
Selectvalue_custom =
VAR Fac = SELECTEDVALUE('Hierarchy'[Facility])
VAR Mar = SELECTEDVALUE('Hierarchy'[Market])
VAR Grp = SELECTEDVALUE('Hierarchy'[Group])
VAR Reg = SELECTEDVALUE('Hierarchy'[Region])

VAR Facility_result = IF (HASONEVALUE('Hierarchy'[Facility]), SELECTCOLUMNS(FILTER(Facility, Facility[Facility] = Fac), "Facility", Facility[Points]), BLANK())
VAR Market_result = IF (HASONEVALUE('Hierarchy'[Market]), SELECTCOLUMNS(FILTER(Market, Market[Market] = Mar), "Market", Market[Points]), BLANK())
VAR Group_result = IF (HASONEVALUE('Hierarchy'[Group]), SELECTCOLUMNS(FILTER(Group, Group[Group] = Grp), "Group", Group[Points]), BLANK())
VAR Region_result = IF (HASONEVALUE('Hierarchy'[Region]), SELECTCOLUMNS(FILTER(Region, Region[Region] = Reg), "Region", Region[Points]), BLANK())

RETURN
    IF (
        HASONEVALUE('Hierarchy'[Region]),
        Region_result,
        IF (
            HASONEVALUE('Hierarchy'[Group]),
            Group_result,
            IF (
                HASONEVALUE('Hierarchy'[Market]),
                Market_result,
                Facility_result
            )
        )
    )

With this modification, when a higher-level hierarchy is selected, it will display the values for that specific level only. If a lower-level hierarchy is selected, it will display the values for that level and hide the values from higher hierarchies.

Please test this updated code with your data and let me know if it works as expected. If you encounter any issues or have further requirements, feel free to share more details, and I'll be happy to assist you further.

I used AI provided by ChatGPT to formulate part of this response. I have verified that the information is accurate before sharing it with you.

If the reply was helpful, please don’t forget to upvote or accept as answer.

Best regards.

Answer 2

Power Bi is not currently supported here on Q&A. We recommend browsing or searching the Power BI documentation for answers to your question - https://learn.microsoft.com/power-bi/

The product group for Power Bi actively monitors questions over at https://community.powerbi.com/

--please don't forget to upvote and Accept as answer if the reply is helpful--