Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Title based on 3 slicers

Hi all,

I'm trying to create a title based on what is selected on 3 slicers. These 3 slicers are hierarchies. 

 

I want a title that shows which profit centre is selected on the slicer. If Profit centre level 3 is selected it will show "NZ Produce Categories" on the report. 

 

If level 4 is selected the title will show "Covered Crops" rather than what is selected in level 3. As below:

Then when level 5 is selected it would show "Capsicums" 

 

Here is the DAX I've tried so far but not working:

Measure Title =
IF (
    HASONEFILTER ( 'Profit Center'[Profit Center Level 3 Text] ),
    SELECTEDVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
    IF (
        AND (
            HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
            HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
        ),
        SELECTEDVALUE ( 'Profit Center'[Profit Center Level 4 Text] ),
        IF (
             (
                HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] )
                    && HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
                    && HASONEVALUE ( 'Profit Center'[Profit Center Level 5 Text] )
            ),
            SELECTEDVALUE ( 'Profit Center'[Profit Center Level 5 Text] )
        )
    )
)

Thanks in advanced!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous managed to figure it out. ended up changing the order of the IF statement and it worked!

    IF (
    HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] )
        && HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
        && HASONEVALUE ( 'Profit Center'[Profit Center Level 5 Text] ),
    SELECTEDVALUE ( 'Profit Center'[Profit Center Level 5 Text] ),
    IF (
        AND (
            HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
            HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
        ),
        SELECTEDVALUE ( 'Profit Center'[Profit Center Level 4 Text] ),
        IF (
            HASONEFILTER ( 'Profit Center'[Profit Center Level 3 Text] ),
            SELECTEDVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
            "TG Fresh"
        )
    )
)

    Thanks for your help.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    In your first if criteria, why not use And to check the criteria together? Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous thanks for the reply! Are you able to provide an example using the AND function please? I don't think it will work as the first criteria I only want to show "Profit Centre level 3 text" value when this is the only slicer selected.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous can you share sample PBIX and expected result so I could help? Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous managed to figure it out. ended up changing the order of the IF statement and it worked!

    IF (
    HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] )
        && HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
        && HASONEVALUE ( 'Profit Center'[Profit Center Level 5 Text] ),
    SELECTEDVALUE ( 'Profit Center'[Profit Center Level 5 Text] ),
    IF (
        AND (
            HASONEVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
            HASONEVALUE ( 'Profit Center'[Profit Center Level 4 Text] )
        ),
        SELECTEDVALUE ( 'Profit Center'[Profit Center Level 4 Text] ),
        IF (
            HASONEFILTER ( 'Profit Center'[Profit Center Level 3 Text] ),
            SELECTEDVALUE ( 'Profit Center'[Profit Center Level 3 Text] ),
            "TG Fresh"
        )
    )
)

    Thanks for your help.