How can I filter one table first, then filter the result of that table using a second table?

[wpf_]

How can I replicate this in dax?  I know i'll need a calculate or calculatetable, but i'm not sure how to filter one table first, then filter the result of that table from a different table. 

The query is something like this:

Select table1_columnA, table2_columnB etc....

from table1, table2

where

table1_columnA = table2_columnA

and table1_columnB  not in (select nvl(table3_columnA,'000000') from table3 where table3_columnA = 'OUT OF SERVICE')

What i want would be the count of the resulting table. 

Thanks

[PijushRoy]

Hi @wpf_ 

Can you share sample data and require output?

[wpf_]

@PijushRoy ,

I can't really share because its huge.  Can you provide me a sample code and the functions i can use?  I just need a little clue to get going.  Thanks.  

[PijushRoy]

@wpf_ 

As requested, I am sharing the concept
VAR _tbl1colA = SELECTEDVALUE('Table1'[ColumnA])
VAR _tbl1colB = SELECTEDVALUE('Table1'[ColumnB])
VAR _tbl2colB = SELECTEDVALUE('Table2'[ColumnB])
VAR _tbl3colA = SELECTEDVALUE('Table3'[ColumnA])
RETURN
SWITCH(
TRUE(),
_tbl1colA=_tbl2colB && NOT _tbl1colB IN ......... && _tbl3colA = "OUT OF SERVICE"

Arrange it as per your requirement. (&& means AND)

If this comment helps you, please LIKE this comment/Kudos
If solve your requirement, please mark this answer as SOLUTION

[wpf_]

@PijushRoy 

I got it working  by using a calculatetable:  It looks something like this:

Table test =

CALCULATETABLE(
TABLE1,
TREATAS(VALUES(TABLE1[TBL1_TBL2ECT]), TABLE2[TBL2_CODE]),
TREATAS(VALUES(TABLE1[TBL1_CODE]), Table3[TBL3_CODE]),
TREATAS(VALUES(TABLE1[TBL1_PPM]), TABLE4[TBL4_CODE]),
TABLE4[TBL4_CLASS] IN {"A", "B", "C", "D" "E" },
Table3[TBL3_VALUE] <> "OUT OF SERVICE",
'Date'[Year Month Name]= "2021-Aug"
)

Question;

1) Since I already have relationships with the tables, I guess i really didn't need to use 'Treatas'.  But what function would I be using in replace of it since I have 4 tables here.  

2) What can i use besides 'Values' if I want repeating values of a column?  

3)How can I do a comparison with two dates columns in this filter?  I want to do something like:

TABLE1[TBL_DateCompleted] <= ( TABLE1[TBL_DUE} + 7 )

But intelesense does not allow me to input the second column: TABLE1[TBL_DUE}

Thanks

[v-janeyg-msft]

Hi, @wpf_ 

Have you take @PijushRoy 's advice? If your problem has been solved, you can mark the answer as solution to close the thread. If not,Please share some sample data(no need to real data) and your desired result and feel free to ask me.

Best Regards,
Community Support Team _ Janey

[wpf_]

@v-janeyg-msft 

What can I use instead of "treatas" if I have the relationships set up already?

Table test =

CALCULATETABLE(
TABLE1,
TREATAS(VALUES(TABLE1[TBL1_TBL2ECT]), TABLE2[TBL2_CODE]),
TREATAS(VALUES(TABLE1[TBL1_CODE]), Table3[TBL3_CODE]),
TREATAS(VALUES(TABLE1[TBL1_PPM]), TABLE4[TBL4_CODE]),
TABLE4[TBL4_CLASS] IN {"A", "B", "C", "D" "E" },
Table3[TBL3_VALUE] <> "OUT OF SERVICE",
'Date'[Year Month Name]= "2021-Aug"
)

[PijushRoy]

@wpf_ 

I think, if you try the calculated column, it is easy for you and solve your question.

3)How can I do a comparison with two dates columns in this filter?  I want to do something like:

TABLE1[TBL_DateCompleted] <= ( TABLE1[TBL_DUE} + 7 )
Yes or create calculate table with Date+7 and use the column

If this comment helps you, please LIKE this comment/Kudos
If solve your requirement, please mark this answer as SOLUTION

[wpf_]

I solved 3).  I had to use the filter function to compare two dates.  

But what can I use instead of treatas in this scenario if I have all the relationships set up already?

Table test =

CALCULATETABLE(
TABLE1,
TREATAS(VALUES(TABLE1[TBL1_TBL2ECT]), TABLE2[TBL2_CODE]),
TREATAS(VALUES(TABLE1[TBL1_CODE]), Table3[TBL3_CODE]),
TREATAS(VALUES(TABLE1[TBL1_PPM]), TABLE4[TBL4_CODE]),
TABLE4[TBL4_CLASS] IN {"A", "B", "C", "D" "E" },
Table3[TBL3_VALUE] <> "OUT OF SERVICE",
'Date'[Year Month Name]= "2021-Aug"
)

[v-janeyg-msft · Accepted solution]

Hi, @wpf_ 

If your relationship is established, you can also use related(table[column]) directly like:

a =
CALCULATETABLE (
    TABLE1,
    RELATED ( TABLE2[TBL2_CODE] ),
    RELATED ( Table3[TBL3_CODE] ),
    RELATED ( TABLE4[TBL4_CODE] ),
    RELATED ( TABLE4[TBL4_CLASS] ) IN { "A", "B", "C", "D", "E" },
    RELATED ( Table3[TBL3_VALUE] ) <> "OUT OF SERVICE",
    'Date'[Year Month Name] = "2021-Aug"
)

Or If the relationship is inactive, Do you consider using 'USERELATIONSHIP' function?

USERELATIONSHIP function (DAX) - DAX | Microsoft Docs

Power BI USERELATIONSHIP vs TREATAS - Enterprise DNA

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[wpf_]

@v-janeyg-msft 

I got it to work by using relatedtable. Thank you for giving me the idea.  

[wpf_]

@v-janeyg-msft ,

Thanks for your reply.  Let me try this and get back to you.  

[PijushRoy]

I think, if you try the calculated column, it is easy for you and solve your question.

3)How can I do a comparison with two dates columns in this filter?  I want to do something like:

TABLE1[TBL_DateCompleted] <= ( TABLE1[TBL_DUE} + 7 )
Yes or create calculate table with Date+7 and use the column

If this comment helps you, please LIKE this comment/Kudos
If solve your requirement, please mark this answer as SOLUTION