Contains

[Fcoatis]

Hello everyone,

I need some help here: Two tables one virtual the other phisical. The virtual returns 18 names and the phisical 35 names I would lke to return A table with all 35 names and true or false if these names appears in the virtual one

DEFINE
VAR T1 =
 DISTINCT(
    SELECTCOLUMNS(
        CALCULATETABLE(
            Arbitragem,
            Arbitragem[Função]="Arbitro",
            Arbitragem[Rodada]>= 12 &&
            Arbitragem[Rodada]<=13
        ),
        "Arbitro", Arbitragem[Nome Oficial]
    )
)

VAR T2 =
DISTINCT(Arbitros[Arbitro])

Please find sample here

Thanks in advance

[ibarrau]

Hi, I don't quite understand if you just want to check data or build a measure. Anyway, this code will return you a table with the names and the new column.

If the tables are related you can write this code:

EVALUATE
ADDCOLUMNS(
	SUMMARIZE( 'Phisical', ' Phisical'[Name] ),
	"NewColumnName",
	IF(
		COUNTROWS(RELATEDTABLE( 'Virtual' )) > 0,
		"True",
		"False"
	)
)

If the tables are not related, something like this should work:

EVALUATE
ADDCOLUMNS(
	SUMMARIZE( 'Phisical', 'Phisical'[name] ),
	"NewColumnName",
	IF(
		'Phisical'[name] IN VALUES ( 'Virtual'[name] ),
		"True",
		"False"
	)
)

Hope this helps

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fcoatis]

Thank you @ibarrau for spending time with this.

Did you check the sample file? Heres what I´m Trying to do.

Test = 
VAR currArbitro = SELECTEDVALUE(Table1[Arbitro])

VAR T1 =
 DISTINCT(
    SELECTCOLUMNS(
        CALCULATETABLE(
            Table1,
            Table1[Rodada]>= 12 &&
            Table1[Rodada]<=13
        ),
        "Arbitro", Table1[Arbitro]
    )
)

VAR T2 = DISTINCT(Arbitros[Arbitro])

RETURN
CONTAINS(t1,[Arbitro],currArbitro)

Can you acomplish in the sample file? Thank you in advance

[ibarrau]

I tried to open the file but the link required permission to access.

The second post you are clarifying the return and it looks like a calculated column. If you want a calculated column just add the "IF" code from my previous post and ignore the rest. The if code is the exact calculated column. Like this

	IF(
		'Phisical'[name] IN VALUES ( 'Virtual'[name] ),
		"True",
		"False"
	)

Hope this help

[Fcoatis]

Weird the shared folder is public.

Try this : Here

[ibarrau]

Here is my answer

https://github.com/ibarrau/PowerBi-code/blob/master/PowerBi%20Reports/Contains.pbix

I have created two columns, one asking if 'customer'[customerkey] is in internetsales and another one asking if 'customer'[customerkey] is in results.

Hope this helps you can download the file from github. I will delete the file and copy the code here if it is solved for you.

[Fcoatis]

Thank you @ibarrau ,

But I cant create relationships. My data model is more complex than the sample. The problem I cant reference a variable inside

VAR currArbitro = SELECTEDVALUE(Table1[Arbitro])

IF( 'Phisical'[name] IN VALUES ( currArbitro ), "True", "False" ) 

Thats why I´d apprecite if could take a look in the file

Thank you so much for your time 

[ibarrau]

I think you are missing something. Maybe a requirement for us or a dax basic. You shouldn't use that currArbitro like you are trying to resolve in a measure. I think that's not going to work.

I have just updated my github with your file. Please check it. I have added a column in all tables asking for arbitro in the others and a visualization about what I think you are trying to accomplish.

Hope this helps because I'm getting confuse on the requirement haha.

[Fcoatis]

@ibarrau Thank you!

I appreciate your time and effort to help me. here is the real scenario if you are still interested.

Once again thanks

[ibarrau]

Hey, did you actually try to add my solution to your scenario? I don't know what else to do with that pbix that should be different of what I already said.

Is the pbix from my github solving the problem? if not, can you try to explain the problem over the real scenario ?

Regards,

[Fcoatis]

Hey, yes I did but the requirements are a bit more complicated.

Here I tried to explain in a document.