When no values selected in a multi-item slicer, show nothing in a line chart

[M_T_Head]

Hi All

I have a simple problem but cannot see any simple answers that work. Most examples I've found go off on tangents to achieve quite clever (but for me, unnecessary) things which makes adapting the suggested DAX difficult for a novice.

In my data model I have a main table which has an Organisation column and I have a standard slicer based on it

I have a Line Chart that uses a measure to plot each organisation's compliance against a common target over time (a simple % calculation)

So far, so good.

All my users are from these organisations and want to see their 'org line' in the chart but will also want to compare their performance to any one or more of the other organisations they select.

I can easily get multiple slicer selections of these organisations to show their individual lines in the chart, however, with nothing filtered by the slicer, ALL these organisaions appear as lines (making it messy) so WHEN NO SELECTION IS MADE I WANT THE CHART TO BE BLANK.

Trying various suggestions from around the web I can get a blank chart that will show a line for a single selection but breaks when a subsequent orgs are selected so I need...

                                          SLICER                                                         LINE CHART

                                Nothing Selected                                                No lines shown

                                Single Org selected                                            Single Org line shown

                                Multiple Orgs selected                                       Multiple lines shown

I assume I'd need to be able to obtain multiple values from the slicer to be placed in the chart's visualisation filter and wrap it round with an IF statement with an 'else' of Blank() but I'm not yet proficient enough with DAX to adapt others' suggestions (dropping the fancy bits). Most suggestions are based on single select options.

Any ideas for a DAX newbie?

Cheers

M

[v-chuncz-msft · Accepted solution]

@M_T_Head,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Empty-visuals-untill-Smart-Filter-is-set/td-p/348880

[M_T_Head]

So I'd just got back from running a victory lap around the garden as I had come up with a solution on my own when I saw Sam's @v-chuncz-msft suggestion and thought 'Can something that simple really do what I want?'

Yes it can. 

So for the benefit of anyone else on this path, having created a disconnected table of relevant organisations, my convoluted DAX was...

OrgSelection := 

         IF(

             NOT(

                    ISCROSSFILTERED(OrgFilter[OrgDescription])) , BLANK(),

                       CALCULATE(

                                          [PercentageCompliance],                                                              ....(my measure for the business bit)

                                               CALCULATETABLE(

                                                       SUMMARIZE('Performance', Performance[Organisation])),

                                                       USERELATIONSHIP(Performance[OrgCode],OrgFilter[OrgCode])

              )))

...which worked as I needed. But adapting Sam's more elegant code....

OrgSelection :=

        IF(

           ISFILTERED(OrgFilter[OrgDescription])

                && SELECTEDVALUE(Performance[OrgDescription]) IN VALUES (OrgFilter[OrgDescription]),

                          CALCULATE(

                                          [PercentageCompliance]), BLANK()

             )

...works just as well, is more like the SQL I'm used to and should be easier to adapt for my other measures supporting other visualizations in my report.

For other PBI newbies, this measure (OrgSelection) goes in the line chart visualization's 'Values' field. With no orgs selected in my slicer I don't see a chart, selecting one org plots a line for it, selecting subsequent ones adds lines for them.

Thanks, Sam!