MultiSelect option set D365 Filter with contains query + OR condition

[PowerBITesting]

FILTER on multiple tables with contains - Not working 

Count= CALCULATE(

COUNT(activity[createdon]),

FILTER(appointments, appointments[Filed1] contains "100") OR

FILTER(CONTAINS(phonecall,phonecall[Filed2],"200")OR

FILTER(CONTAINS(tasks,tasks[Filed3],"200")

)

appointments , phonecall and tasks  all are child of Activity.

and Filed1 ,Filed2 and Filed3 are multioption set D365

[v-xuding-msft · Accepted 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
        )
    )

[amitchandak]

@PowerBITesting , Try like

Count= CALCULATE(
COUNT(activity[createdon]),
OR(OR(appointments[Filed1] contains "100") , CONTAINS(phonecall[Filed2],"200") , CONTAINS(tasks[Filed3],"200"))
)

[lbendlin]

DAX uses || for the "or" logic and && for the "and" logic.