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.
"AD CalendarDate" and "DiscoveryDate: are linked = Many to Many
Each "Activity ID" (left) has a "AD ProgramName"
Both tables have "AD Genesis ID" and "JobSeekerID". I know I need to link those.
I would like to see the highest count of "AD ProgramName" per "AD GenesisID" / "JobSeekerID" when there is a "PlacementNumber"
Solved! Go to Solution.
Hi @dave24meyers ,
According to your description, I suggest you make relationship by "AD Genesis ID" and "JobSeekerID" columns.
I create a sample.
Table1
Table2
In this sample, the highest count of "AD ProgramName" per "AD GenesisID" / "JobSeekerID" when there is a "PlacementNumber" should be 2(the count of AD GenesisID is 2).
Here's my solution, create a measure.
Max Count =
VAR _T =
SUMMARIZE (
'Table1',
'Table1'[AD GenesisID],
"Count",
CALCULATE (
COUNT ( 'Table1'[AD ProgramName] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[AD GenesisID] = MAX ( 'Table1'[AD GenesisID] )
),
NOT ( 'Table2'[PlacementNumber] = BLANK () )
)
)
RETURN
MAXX ( _T, [Count] )
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dave24meyers ,
According to your description, I suggest you make relationship by "AD Genesis ID" and "JobSeekerID" columns.
I create a sample.
Table1
Table2
In this sample, the highest count of "AD ProgramName" per "AD GenesisID" / "JobSeekerID" when there is a "PlacementNumber" should be 2(the count of AD GenesisID is 2).
Here's my solution, create a measure.
Max Count =
VAR _T =
SUMMARIZE (
'Table1',
'Table1'[AD GenesisID],
"Count",
CALCULATE (
COUNT ( 'Table1'[AD ProgramName] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[AD GenesisID] = MAX ( 'Table1'[AD GenesisID] )
),
NOT ( 'Table2'[PlacementNumber] = BLANK () )
)
)
RETURN
MAXX ( _T, [Count] )
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As @Greg_Deckler mentioned, Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Hi, @dave24meyers
In case that these two tables are fact, I would not recommed many-to-many. If your data allow to use (extract) dimensions as tables related (single) to both fact tables will be better.
Proud to be a Super User!
@dave24meyers Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |