Dax Filter 2 tables

[Anonymous]

Hi,

I have 2 related table many to one

Table 1

Name     category

A	Category 1
B	Category 1
C	Category 2
D	Category 2
E	Category 2
F	Category 3
G	Category 4
H	Category 4
I	Category 5
J	Category 6
K	Category 7
L	Category 8
M	Category 9

Table 2

Name     Policy      Category 

A	WP	Category 1
A	Fixed	Category 1
A		Category 1
A	WP	Category 1
B	Fixed	Category 1
B	Fixed	Category 1
B	Fixed	Category 1
B	Fixed	Category 1
C	Fixed	Category 2
C	Fixed	Category 2
C	Fixed	Category 2
C	Fixed	Category 2
D	WP	Category 2
D	Fixed	Category 2
D	WP	Category 2
D	WP	Category 2
E	WP	Category 2
E	Fixed	Category 2
E	WP	Category 2
E	WP	Category 2
F	WP	Category 3
F	Fixed	Category 3
F	WP	Category 3
F	WP	Category 3
G	Fixed	Category 4
G	Fixed	Category 4
G	Fixed	Category 4
G	Fixed	Category 4
H	Fixed	Category 4
H	WP	Category 4
H		Category 4
H	WP	Category 4
H	ADER	Category 4

I got the error message when I use this filter : 

 A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

measure = 

var  _Conform = Calculate(Distinctcount(Table1[Name], Table1[Category] IN {"Category 1", "Category 2", "Category 3", "Category 4"} && Filter(Table2, Table2[Policy] <> "WP"))

var  _Total = Calculate(Distinctcount(Table1[Name], Table1[Category] IN {"Category 1", "Category 2", "Category 3", "Category 4"} )
Return Rounddown(_Conform/_Total,2)

 It works If I remove Filter(Table2, Table2[Policy] <> "WP")

Could you please advide the solution?

Many thanks in advance.

[lbendlin]

MEASURE =
VAR a =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, [Name] ),
        "has WP", CALCULATE ( COUNTROWS ( Table2 ), Table2[Policy] = "WP" )
    )
RETURN
    ROUNDDOWN (
        DIVIDE ( COUNTROWS ( FILTER ( a, [has WP] = 0 ) ), COUNTROWS ( a ), 0 ),
        2
    )

[Anonymous]

@lbendlin Hi, Many thanks for your quick reply. The measure works, I would like to sort out the list of Conform and No Conform, could you please advise the measure to count the number of Conform and Non Conform.

If the same Name has FIXED and WP, this Name is not conformed. 

Only the Name without any WP is Conform

Thank you again. 

Regards,

Tg

[lbendlin · Accepted solution]

conform:  COUNTROWS ( FILTER ( a, [has WP] = 0 ) )

non-conform: COUNTROWS ( FILTER ( a, [has WP] > 0 ) )

[Anonymous]

@lbendlin Many thanks for your help!

😀