Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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?





Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





@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







Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





@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



Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





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
 



Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.