cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

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

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

1 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

 

View solution in original post

12 REPLIES 12
PijushRoy
Super User
Super User

Hi @wpf_ 

Can you share sample data and require output?


@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.  

@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




@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

 

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

@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"
)

@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

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"
)

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

 

@v-janeyg-msft 

 

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

@v-janeyg-msft ,

 

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

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
 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.