topic Unmatched Row count with aggregation with Region in Report Server

Unmatched Row count with aggregation with Region

Microblog321 — Mon, 13 Dec 2021 14:03:04 GMT

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

Re: Unmatched Row count with aggregation with Region

Microblog321 — Sun, 12 Dec 2021 18:38:56 GMT

Can someone please help me

Re: Unmatched Row count with aggregation with Region

Microblog321 — Mon, 13 Dec 2021 14:54:16 GMT

can someone please help

Re: Unmatched Row count with aggregation with Region

Icey — Tue, 14 Dec 2021 05:32:34 GMT

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.