Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Use Slicer data in a smart narrative

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

Need Help
  • Pragati11's avatar
    Pragati11
    4 years ago

    HI Anonymous ,

     

    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.

     

     

3 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    4 years ago

    HI Anonymous ,

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Pragati11
        Icon for Super User rankSuper User
        4 years ago

        Hi Anonymous ,

         

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