Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
For a project I currently have two tables, Table1 and Table2, with at least column A and B that can contain any value.
I am trying to evaluate multiple criteria per row.
Criteria 1: Both column A and B of Table1 and Table2 contain identical values on a row to their respective columns. I solved this using CONTAINS(Table1,Table1[A],Table2[A],Table1[B],Table2[B])
Criteria 2: Column A containts an identical value in Table1 and Table2 AND Column B is empty in Table 1 and column B contains any value in Table2. I would expect a solution to come in the form of IF(ISBLANK(Table1[B]), CONTAINS(Table1,Table1[A],Table2[A],Table2[B],"*"))
However this does not produce the desired result as it does not evaluate the "*" it seems. How would one find multiple criteria. A MWE of the tables and the expected outcome is provided underneath.
Table 1
A | B | Expected Outcome |
Req-032 | X | True |
Req-0652 | Y | True |
Req-0652 | X | False |
0042 | True | |
Req-024 | False |
Table 2
A | B |
Req-032 | X |
Req-0652 | Y |
Req-0652 | Z |
0042 | A |
Req-024 |
Solved! Go to Solution.
Hi, @Shippior
Try to create a measure like this:
result =
VAR _isB_notBlank =
NOT ( ISBLANK ( MAX ( 'Table1'[B] ) ) )
VAR _T2_BinA =
SUMMARIZE ( FILTER ( ALL ( Table2 ), 'Table2'[A] = MAX ( 'Table1'[A] ) ), [B] )
VAR _if =
IF (
_isB_notBlank,
IF ( MAX ( 'Table1'[B] ) IN _T2_BinA, "True", "False" ),
MAXX ( _T2_BinA, [B] ) <> BLANK ()
)
RETURN
_if
result:
Please refer to the attachment below for details
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, @Shippior
Try to create a measure like this:
result =
VAR _isB_notBlank =
NOT ( ISBLANK ( MAX ( 'Table1'[B] ) ) )
VAR _T2_BinA =
SUMMARIZE ( FILTER ( ALL ( Table2 ), 'Table2'[A] = MAX ( 'Table1'[A] ) ), [B] )
VAR _if =
IF (
_isB_notBlank,
IF ( MAX ( 'Table1'[B] ) IN _T2_BinA, "True", "False" ),
MAXX ( _T2_BinA, [B] ) <> BLANK ()
)
RETURN
_if
result:
Please refer to the attachment below for details
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@v-angzheng-msft Awesome, this works.
Although I don't quite understand why this works as a measure instead of a column.
@Shippior Use a COUNTROWS of INTERSECT
Unfortunately I can not use INTERSECT as the tables are not limited to Column A and B, but also included C,D, etc.
How would using COUNTROWS provide the expected outcome? I am thinking of CALCULATE(COUNTROWS(Table2),FILTER(NOT(ISBLANK(Table1[B]))) > 0 but currently I have no relationship between these two Tables so that does not work unfortunately.
@Shippior Let me be more clear and I also think I understand the problem differently.
Create a table variables using SELECTCOLUMNS to select just your 1 column, column B in Table A. You can then get the current value of Column B in context in your other table in a different VAR. You can then use CONTAINS or the IN operator.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
111 | |
104 | |
85 | |
65 | |
63 |