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
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
Solved! Go to 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
Hi @wpf_
Can you share sample data and require output?
Proud to be a Super User! | |
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
Proud to be a Super User! | |
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:
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
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"
)
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:
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
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:
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
95 | |
92 | |
86 | |
69 |
User | Count |
---|---|
162 | |
129 | |
125 | |
104 | |
98 |