Left outer join and Filter not working in dax

[Anonymous]

Hi experts,

Having this table

IDCategoria

ID1	Cat1
ID1	Cat2
ID1	Cat4
ID2	Cat3
ID3	Cat1
ID2	Cat1
ID5	Cat4
ID5	Cat1
ID5	Cat2
ID5	Cat3

I want to retrieve ID's in category 1, that are not present in category 2. In this example ID1,ID2,ID3,ID5 belong to "Cat1" and ID1,ID5, belongs to "Cat2". So I want to retrieve ID2 and ID3 as they are not present in 2Cat2", but is not working

Only1 = 
VAR _cat = { "Cat1"; "Cat2" }
RETURN 
CALCULATE(
    IF( 
        COUNTA( MiTabla[Categoria] ) = COUNTX( _cat; [Value] ); 1 
    );
    FILTER(MiTabla; MiTabla[Categoria] ="Cat1");
    FILTER(MiTabla; NOT(MiTabla[Categoria]) IN {"Cat2"})
)
Also this =
CALCULATE(
    IF( 
        COUNTA( MiTabla[Categoria] ) = COUNTX( _cat; [Value] ); 1 
    );
    FILTER(MiTabla; MiTabla[Categoria] IN {"Cat1"} &&  NOT(MiTabla[Categoria]) IN {"Cat2"})
)

It should be straightforward as I can do the opposite. Any suggestion pls?

[Ashish_Mathur]

Hi,

Are Cat1 and Cat2 the only 2 categories that you want to study or would you want to study other Categories as well - such as Cat1 and Cat3, Cat2 and Cat4?

[Anonymous]

Hi @Ashish_Mathur . Other categories too. 

[Ashish_Mathur]

Hi,

Has your problem been solved?

[dax · Accepted solution]

Hi danimelv,

You could create table like below

Table 2 = CALCULATETABLE( 
    VALUES ( MiTabla[ID] ),
    FILTER ( ALL(MiTabla ),MiTabla[categoria]="Cat2"
))

Then create a measure like below

Only1 =
VAR temp =
    SELECTEDVALUE ( 'MiTabla'[ID] )
RETURN
    IF (
        NOT ( temp IN VALUES ( 'Table 2'[ID] ) )
            && MIN ( MiTabla[categoria] ) = "Cat1",
        0,
        1
    )

Then apply filter like below, you will get result 

Best Regards,
Zoe Zhi

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