Requesting help with DAX Measure OR Custom Column

[dave24meyers]

"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"

[v-yanjiang-msft · Accepted 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.

[VahidDM]

Hi @dave24meyers 

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/

[ALLUREAN]

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. 

[Greg_Deckler]

@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.