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.