Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
FILTER on multiple tables with contains - Not working
Solved! Go to Solution.
Hi @PowerBITesting ,
You could try like this:
Column =
VAR app_id =
CALCULATE (
FIRSTNONBLANK ( appointments[ID], 1 ),
FILTER (
VALUES ( appointments[Filed1] ),
SEARCH ( "100", appointments[Filed1],, 999 ) <> 999
)
)
VAR pho_id =
CALCULATE (
FIRSTNONBLANK ( phonecall[ID], 1 ),
FILTER (
VALUES ( phonecall[Filed2] ),
SEARCH ( "100", phonecall[Filed2],, 999 ) <> 999
)
)
VAR task_id =
CALCULATE (
FIRSTNONBLANK ( tasks[ID], 1 ),
FILTER (
VALUES ( tasks[Filed3] ),
SEARCH ( "100", tasks[Filed3],, 999 ) <> 999
)
)
RETURN
COUNTROWS (
FILTER (
activity,
activity[app_id] = app_id
|| activity[pho_id] = pho_id
|| activity[task_id] = task_id
)
)
Hi @PowerBITesting ,
You could try like this:
Column =
VAR app_id =
CALCULATE (
FIRSTNONBLANK ( appointments[ID], 1 ),
FILTER (
VALUES ( appointments[Filed1] ),
SEARCH ( "100", appointments[Filed1],, 999 ) <> 999
)
)
VAR pho_id =
CALCULATE (
FIRSTNONBLANK ( phonecall[ID], 1 ),
FILTER (
VALUES ( phonecall[Filed2] ),
SEARCH ( "100", phonecall[Filed2],, 999 ) <> 999
)
)
VAR task_id =
CALCULATE (
FIRSTNONBLANK ( tasks[ID], 1 ),
FILTER (
VALUES ( tasks[Filed3] ),
SEARCH ( "100", tasks[Filed3],, 999 ) <> 999
)
)
RETURN
COUNTROWS (
FILTER (
activity,
activity[app_id] = app_id
|| activity[pho_id] = pho_id
|| activity[task_id] = task_id
)
)
@PowerBITesting , Try like
Count= CALCULATE(
COUNT(activity[createdon]),
OR(OR(appointments[Filed1] contains "100") , CONTAINS(phonecall[Filed2],"200") , CONTAINS(tasks[Filed3],"200"))
)
DAX uses || for the "or" logic and && for the "and" logic.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |