Unmatched Row count with aggregation with Region

[Microblog321]

Hello Friends,  

Please help me, I am new to Power bi and looking for the desired output for a week now but no luck, 

I have 5 tables in which 3 are dimtable (Dim_date,Dim_Taskid,Dim_Region) and 2 table as (Table1 and Table2) and these 2 table are connected to Dim_table with M-to 1 relationship. Table 1 and 2 has duplicate ,Date, Taskid,Region.

let me break down the measure i am looking for  as below.

when i  filter dates it should 

1. First compare Table1 with Table 2 and select all the taskid which are unmatched. it should select the distinct match.

2. then aggregate(Sum or count) unmatched  Taskid (Which is a output from point 1)  with region.

Please see the output

Let me explain it with example

1. First select all the taskid matched as per filtered date(Jan-Aug) .

2Then count the taskid with Region .

Regards

[Icey · Accepted solution]

Hi @Microblog321 ,

Please check if this could meet your requirements:

unmatched Taskid Count =
VAR t =
    DISTINCT (
        SUMMARIZE (
            EXCEPT (
                SUMMARIZE ( 'Table 1', 'Table 1'[Taskid], 'Table 1'[Region] ),
                SUMMARIZE ( 'Table 2', 'Table 2'[Taskid], 'Table 2'[Region] )
            ),
            [Taskid]
        )
    )
RETURN
    COUNTX ( t, [Taskid] )

matched Taskid Count = 
VAR t =
    DISTINCT (
        SUMMARIZE (
            INTERSECT (
                SUMMARIZE ( 'Table 1', 'Table 1'[Taskid], 'Table 1'[Region] ),
                SUMMARIZE ( 'Table 2', 'Table 2'[Taskid], 'Table 2'[Region] )
            ),
            [Taskid]
        )
    )
RETURN
    COUNTX ( t, [Taskid] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Microblog321]

Can someone please help me

[Microblog321]

can someone please help