Forum Discussion
CONTAINS() with multiple values
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
)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
)
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.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=sharingHi 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.