Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Microblog321
Frequent Visitor

Unmatched Row count with aggregation with Region

Capture.PNG

 

 

 

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 .

 

Capture1.PNG

 

Regards

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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] )

Icey_0-1639459730245.png

Icey_1-1639459917231.png

 

 

Best Regards,

Icey

 

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

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

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] )

Icey_0-1639459730245.png

Icey_1-1639459917231.png

 

 

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
Frequent Visitor

Can someone please help me

can someone please help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.