Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

URGENT!! DAX help required

We have a hierarchical slicer in Power BI (screenshot below) with data from three columns: Global, US_OUS, and Region. We want to have the chart titles be dynamic based on the selected combination as follows.

    • Global: to be used in chart title if the US, all regions of Int’l  and Residual Global are selected
    • US: to be used in chart title if the US is selected
    • Int’l: to be used in chart title only if all the sub-regions within Int’l (e.g. WEC, EEMEA, LATAM, JAPAC, Residual Int’l) are selected
      • If only a few of the sub-regions are selected then don’t show the region on the chart title
      • We are facing an issue with the third condition because the regions under Int'l can vary over time. Using the below example:
  • We should not show any region because only two under Int'l are selected. 
  • The chart title should be Int'l when all countries under it are selected.

 

 

 

Need Help
Tips and Tricks
Tutorial Requests
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous ,

     

    Maybe you could try this Measure.

    Dynamic chart titles =
VAR selected_US_OUS =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        FILTER ( ALLSELECTED ( 'Table'[Region] ), ISFILTERED ( 'Table'[Global] ) )
    )
VAR countTables =
    COUNTROWS ( ALL ( 'Table' ) )
VAR selectedUS_OUS =
    SELECTEDVALUE ( 'Table'[US_OUS] )
VAR count_Int_l =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        FILTER (  'Table' , 'Table'[US_OUS] = "Int'l" )
    )
VAR selected_Int_l =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        ALLSELECTED('Table'[Region])
    )
RETURN
    IF (
        selected_US_OUS = countTables,
        "Global",
        IF (
            selectedUS_OUS = "US",
            "US",
            IF ( count_Int_l = selected_Int_l, "Int'l", "Unknown" )
        )
    )

     

    The result looks like this.

     

     

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

1 Reply

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Maybe you could try this Measure.

    Dynamic chart titles =
VAR selected_US_OUS =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        FILTER ( ALLSELECTED ( 'Table'[Region] ), ISFILTERED ( 'Table'[Global] ) )
    )
VAR countTables =
    COUNTROWS ( ALL ( 'Table' ) )
VAR selectedUS_OUS =
    SELECTEDVALUE ( 'Table'[US_OUS] )
VAR count_Int_l =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        FILTER (  'Table' , 'Table'[US_OUS] = "Int'l" )
    )
VAR selected_Int_l =
    CALCULATE (
        COUNT ( 'Table'[Region] ),
        ALLSELECTED('Table'[Region])
    )
RETURN
    IF (
        selected_US_OUS = countTables,
        "Global",
        IF (
            selectedUS_OUS = "US",
            "US",
            IF ( count_Int_l = selected_Int_l, "Int'l", "Unknown" )
        )
    )

     

    The result looks like this.

     

     

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun