Except and sum filtered columns

[carmen_romero]

Hi!

I have the next problem and my table looks like this one:

I need generate a measure that filter col1 by some value of Col2, Col2 == Field2 for example, and Col3 by Col2==Field1, then I want to do a except between Col1 and Col3, and finally Sum the number of distinct rows by Col1. This will be my measure, is it possible to do this?

Thx.

[az38]

@carmen_romero 

based on your data example what result do you expect?

[carmen_romero]

Hi! thx for your response!

I need to follow this steps:

1 - Filter Col1 by value of Col2 in this case Col2 == FieldB1

2 - Filter Col3 by value of Col2 in this case Col2 == FieldB3

3- Select from result of step 1 the Col1 and the step 2 the Col3

4 - Do a except between values of both columns: Col1 except Col3

5 - Count distinct values for the except in the Col1

Thx!

[v-easonf-msft · Accepted solution]

Hi , @carmen_romero 

Here is  a demo.

pbix attached 

If help , try steps as below.

1.create  two Separation table as slicer

Measure 1 = CALCULATE(DISTINCTCOUNT('Table'[Col3]),FILTER('Table','Table'[Col2]="FieldB1"))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Col1]),FILTER('Table','Table'[Col2]="FieldB1"))

2.create measure "measure value" as below and  put it into card visual

measure value =
VAR t1 =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Col1] ),
        FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table 1'[Slicer1] ) )
    )
VAR t2 =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Col3] ),
        FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table2'[Slicer2] ) )
    )
RETURN
    SUMX ( t1, IF ( [Col1] IN t2, 1, 0 ) )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.