Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Left outer join and Filter not working in dax

Hi experts,

Having this table

 

IDCategoria

ID1Cat1
ID1Cat2
ID1Cat4
ID2Cat3
ID3Cat1
ID2Cat1
ID5Cat4
ID5Cat1
ID5Cat2
ID5Cat3

 

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?

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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 

331.PNG

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.

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur . Other categories too. 

Hi,

Has your problem been solved?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dax
Community Support
Community Support

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 

331.PNG

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.