Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
Can someone please help me
can someone please help
User | Count |
---|---|
11 | |
9 | |
4 | |
1 | |
1 |