Forum Discussion
Dax Filter 2 tables
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.
conform: COUNTROWS ( FILTER ( a, [has WP] = 0 ) )
non-conform: COUNTROWS ( FILTER ( a, [has WP] > 0 ) )
4 Replies
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
)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
conform: COUNTROWS ( FILTER ( a, [has WP] = 0 ) )
non-conform: COUNTROWS ( FILTER ( a, [has WP] > 0 ) )