Filter Results without reducing the values in the column

[Brett007]

I would like to filter results to show all locations with Product Line A and show all of the other Product Lines produced in that location.

This is the bad result

I would like to see the other Product Lines made in those locations at the same time.

This is the Measure that I use to generate the "List of Product Line values" column

Main table of values is 'allChart'

Let me know if you have any ideas.

Thank you,

-Brett

[OwenAuger · Accepted solution]

Hi @Brett007 

The solution I would suggest involves using ALL ( 'allChart'[Product Line] ) to clear the filter on Product Line when determining the list of Product Line values

In the code below, ValuesComplete is a list of Product Lines with the Product Line filter cleared.

I also rewrote the code slightly, using the fact to TOPN will return all the values if there are fewer than N.

Does this work as expected?

List of Product Line values = 
VAR __MAX_VALUES_TO_SHOW = 100
VAR ValuesComplete =
    CALCULATETABLE (
        VALUES ( 'allChart'[Product Line] ),
        ALL ( 'allChart'[Product Line] )
    )
VAR __DISTINCT_VALUES_COUNT =
    COUNTROWS ( ValuesComplete )
VAR DisplayEtc =
    __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW
VAR Result =
    CONCATENATEX (
        -- TopN can be used even if num items is smaller than max
        TOPN (
            __MAX_VALUES_TO_SHOW,
            ValuesComplete,
            'allChart'[Product Line], ASC
        ),
        'allChart'[Product Line],
        ", ",
        'allChart'[Product Line], ASC
    )
        & IF (
            DisplayEtc,
            ", etc."
        )
RETURN
    Result

[Brett007]

Works perfectly.  Thank you.