Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Requesting help with DAX Measure OR Custom Column

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

Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    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.

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

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

  • ALLUREAN's avatar
    ALLUREAN
    Solution Sage
    4 years ago

    Hi, Anonymous 

    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. 

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    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/

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    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.