Use Slicer data in a smart narrative

[Johnweet]

I'm creating a report that my team members can export to PowerPoint and send to their stakeholders.  the first page of the report has multiple slicers that impact all pages.  This is so my team can show data that is relevant to their audiences only.  I want to be able to show those selections on each slide.  I could replicate the slicers on each report page but that would just take up too much real estate on each slide.  Ideally what i want is a smart footnote that shows all the slicer selections.  I thought i could do it with smart narrative but can't figure out how.  Any ideas

[Pragati11 · Accepted solution]

HI @Johnweet ,

You can try creating a measure using the values concatenated from all the slicer selections. Then display this measure as a footer on your report.

Something like below:

(I have displayed the footer on a multi-card visual as this will always be a single value based on the selections from the slicers)

The measure that I have used is:

Conditional Title = 
VAR prodName = VALUES('Supermarket Sales'[Product line])
VAR occur = COUNTROWS(ALL('Supermarket Sales'[Product line])) = COUNTROWS(prodName)
RETURN
IF(
    occur, "Summary of Products",
    CONCATENATE("Summary of Product Line: ",
        CONCATENATEX(
            prodName,
            'Supermarket Sales'[Product line], 
            ", "
        )
    )
)

Currently, it takes into account selection from a single slicer, but this can be modified as per requirement to show selections from multiple slicers.

[Johnweet]

Thanks Pragati. I couldn't get your formula to work so started with a Quick measure and created the following.

List of TEXT_FISCAL_YEAR values =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Fiscal Calendar'[TEXT_FISCAL_YEAR])

VAR __MAX_VALUES_TO_SHOW = 5

RETURN

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW,

                    ALLSELECTED('Fiscal Calendar'[TEXT_FISCAL_YEAR]),

                    'Fiscal Calendar'[TEXT_FISCAL_YEAR],

                    ASC

                ),

                'Fiscal Calendar'[TEXT_FISCAL_YEAR],

                ", ",

                'Fiscal Calendar'[TEXT_FISCAL_YEAR],

                ASC

            ),

            ", etc."

        ),

        CONCATENATEX(

            ALLSELECTED('Fiscal Calendar'[TEXT_FISCAL_YEAR]),

            'Fiscal Calendar'[TEXT_FISCAL_YEAR],

            ", ",

            'Fiscal Calendar'[TEXT_FISCAL_YEAR],

            ASC

        )

    )

I then thought I would create another quick measure for one of the other slicers and add it to the one above. I must have pasted it in the wrong place because it didn't work.  Where do i need to past it and how many of these could I nest in the above formula.  It looks like you can only concatenate two values.

[Pragati11]

Hi @Johnweet ,

You will need nested CONCATENATE statements to get multiple selections in the measure.