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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Community Champion
Community Champion

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.  

PijushRoy
Community Champion
Community Champion

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

PijushRoy
Community Champion
Community Champion

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

PijushRoy
Community Champion
Community Champion

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors