CONTAINS() with multiple values

[Anonymous]

Hi folks

I am not the first one with this problem I am sure and I have done a lot searching, but with no viable solution.

I am struggling with a virtual relationship. I have one dimension Table "Documents_Relevant" where I define which document is relevant for which business line (this is what I have in my slicer). There's also the central location dim (ALL_Dim_Location) table which also contains the BusinessLine for each station. Station_Acknowledgement is my fact table.
I can't let the two dimension tables filter each other as it would create a circular dependency. I want to make sure that whenever a BusinessLine is selected, only the relevant documents are shown (done this with a relationship) but also only the list of stations are shown where that businessline exists.

So the RelevantforBusinessLines selection should filter the location DIM so that only those stations are shown which are relevant:

Goal is to use the measure "Filter BL" as a filter (Filter BL =1) on the report page to create that relationship. It works without issues if only one (or none) value is selected in the slicer. But how can I pass multiple values from the [RelevantforBusinesslines] into the Location table so that for each selected [RelevantforBusinesslines] it would also generate a "1" for the related [BusinessLineLabel] in the ALL_Dim_Location?

Filter BL =

VAR _NrOfSelectedBL =
    DISTINCTCOUNT ( Documents_Relevant[RelevantforBusinesslines] )
RETURN
    IF (
        ISFILTERED ( Documents_Relevant[RelevantforBusinesslines] );
        IF (
            _NrOfSelectedBL = 1;
            IF (
                CONTAINS (
                    'PowerBI_DCMAdmin ALL_Dim_Location';
                    'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel]; VALUES ( Documents_Relevant[RelevantforBusinesslines] )
                );
                1;
                0
            );
            IF (
                _NrOfSelectedBL > 1;
                IF (
                    CONTAINS (
                        'PowerBI_DCMAdmin ALL_Dim_Location';
                        'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel]; VALUES ( Documents_Relevant[RelevantforBusinesslines] )
                    );
                    1;
                    0
                )
            )
        );
        1
    )

 Thanks a lot for the help!

[MFelix]

Hi @Anonymous ,

Try the following code:

Filter BL =
VAR _NrOfSelectedBL =
    DISTINCTCOUNT ( Documents_Relevant[RelevantforBusinesslines] )
RETURN
    IF (
        ISFILTERED ( Documents_Relevant[RelevantforBusinesslines] );
        IF (
            _NrOfSelectedBL = 1;
            IF (
                SELECTEDVALUE ( 'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel] )
                    IN VALUES ( Documents_Relevant[RelevantforBusinesslines] );
                1;
                0
            );
            IF (
                _NrOfSelectedBL > 1;
                IF (
                    SELECTEDVALUE ( 'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel] )
                        IN VALUES ( Documents_Relevant[RelevantforBusinesslines] );
                    1;
                    0
                )
            )
        );
        1
    )

Also try this simplified version:

Filter BL =
VAR _NrOfSelectedBL =
    DISTINCTCOUNT ( Documents_Relevant[RelevantforBusinesslines] )
RETURN
    SWITCH (
        TRUE ();
        ISFILTERED ( Documents_Relevant[RelevantforBusinesslines] )
            && _NrOfSelectedBL = 1
            && SELECTEDVALUE ( 'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel] )
                IN VALUES ( Documents_Relevant[RelevantforBusinesslines] ); 1;
        ISFILTERED ( Documents_Relevant[RelevantforBusinesslines] )
            && _NrOfSelectedBL > 1
            && SELECTEDVALUE ( 'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel] )
                IN VALUES ( Documents_Relevant[RelevantforBusinesslines] ); 1;
        0
    )

[Anonymous]

Hi @MFelix 

Thanks for the hint of using IN for my formula. I tried your attempts as well but it seems the whole thing is not working properly, the result is not what I expect when selecting multiple items in [RelevantForBusinessLines]. It comes up with starange set of locations... the SWITCH would only work if it was nested into an IF in the first place as I need the output 1 when nothing is filtered.

I tried accomodating your suggestion into my old formula, but again, as soon as I have more than one selected, it does not work for some reason and my guess is that it has to do with SELECTEDVALUE() which only works for one value, not multiple ones...

If I select one businessline with only one location, it shows. When I add another one with only one location you would expect to see a total of 2, but instead it remains blank.

This is where I am right now:

Filter BL = 

VAR _NrOfSelectedBL =
    DISTINCTCOUNT ( Documents_Relevant[RelevantforBusinesslines] )
RETURN
    IF (
        ISFILTERED ( Documents_Relevant[RelevantforBusinesslines] );
        IF (
            _NrOfSelectedBL = 1;
            IF (
                CONTAINS (
                    'PowerBI_DCMAdmin ALL_Dim_Location';
                    'PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel]; VALUES ( Documents_Relevant[RelevantforBusinesslines] )
                );
                1;
                0
            );
            IF (
                _NrOfSelectedBL > 1;
                IF (
                    SELECTEDVALUE('PowerBI_DCMAdmin ALL_Dim_Location'[BusinessLineLabel]) IN VALUES(Documents_Relevant[RelevantforBusinesslines]);
                    1;
                    0
                )
            )
        );
        1
    )

[MFelix]

Hi @Anonymous ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

@MFelix 

Thanks! I built a mockup of the same scenario now and during that I reliazed that it works fine as long as the BusinessLine is in the visual. A soon as you kick out the BusinessLine, e.g. only showing the station, it no longer works... strange?
Here's a link to my google drive with the PBIX: https://drive.google.com/drive/folders/1AYCsvpI_UYscEqAYzlSkkY9a3G9Zn3NY?usp=sharing 

[MFelix]

Hi @Anonymous ,

This is the normal behaviour. Measures in dax are based in context and context is given by a lot of different options such as slicers, filters, columns in visualizations, other measures even syntax variables.

So in order to make a comparision of Bussiness line in a visualization you need to have it in the visualization, offcourse you can force that with an ALLSELECTED or similar but depends on the result you need.