Forum Discussion

Microblog321's avatar
Microblog321
Frequent Visitor
4 years ago
Solved

Unmatched Row count with aggregation with Region

 

 

 

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

Match
  • Icey's avatar
    Icey
    4 years ago

    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.

3 Replies

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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.