Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
7 | |
3 | |
3 | |
2 |
User | Count |
---|---|
15 | |
11 | |
5 | |
5 | |
2 |