Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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?
Hi,
Has your problem been solved?
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |