Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

CONTAINS() with multiple values

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!

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        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.