Return in a new table the most counted text variable from several others tables

[Anonymous]

Hi everyone ! I have several tables who looks like this :

Sample data

NewTable = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "RandomYear", ROUNDUP(RAND() * 4 + 2020, 0),
    "RandomName", 
        SWITCH(RANDBETWEEN(1, 10), 
            1, "Alice", 
            2, "Bob", 
            3, "Charlie", 
            4, "David", 
            5, "Emma", 
            6, "Frank", 
            7, "Grace", 
            8, "Henry", 
            9, "Isabel", 
            10, "Jack"
        ),
    "RandomNote", 
        SWITCH(RANDBETWEEN(1, 3), 
            1, "A", 
            2, "B", 
            3, "C"
        ),
    "RandomKey", 
        CONCATENATEX(
            GENERATESERIES(1, 6, 1),
            FORMAT(RAND() * 10, "0"), 
            ""
        )
)

I want to be able to create a new table, which returns from my different tables, for each name and Primary_key (random_key), the note that is most counted for each year, how can I do that with DAX code ?

Really thanks for anyone who can help ! (If is possible to do this with DAX whitout have to go through modelisation ?)

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Create a calculated column for each table to get the count of notes for each year.

Count =
COUNTX (
    FILTER (
        ALL ( 'NewTable' ),
        'NewTable'[RandomYear] = EARLIER ( 'NewTable'[RandomYear] )
            && 'NewTable'[RandomNote] = EARLIER ( 'NewTable'[RandomNote] )
    ),
    [RandomNote]
)

2. Create a calculated table and merge the previous tables.

True Table =
VAR _table1 =
    SUMMARIZE ( 'NewTable', [RandomYear], [RandomNote], [Count] )
VAR _table2 =
    SUMMARIZE ( 'NewTable1', [RandomYear], [RandomNote], [Count] )
VAR _table3 =
    SUMMARIZE ( 'NewTable2', [RandomYear], [RandomNote], [Count] )
VAR _table =
    UNION ( _table1, _table2, _table3 )
RETURN
    SUMMARIZE (
        _table,
        [RandomYear],
        [RandomNote],
        "Sum",
            SUMX (
                FILTER (
                    _table,
                    [RandomYear] = EARLIER ( [RandomYear] )
                        && [RandomNote] = EARLIER ( [RandomNote] )
                ),
                [Count]
            )
    )

3. Create a measure to get the note with the highest count.

Flag =
VAR _max =
    MAXX (
        FILTER ( ALL ( 'True Table' ), [RandomYear] = MAX ( 'True Table'[RandomYear] ) ),
        [Sum]
    )
RETURN
    IF ( _max = MAX ( 'True Table'[Sum] ), 1, 0 )

4. Filter out flags with a value of 1 in the filter.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Man this is the perfect just it is possible to separate the count fort the Random Note in the ending table and have the primary key associated with it ? And Have column for RandomNote1 (for table1), RandomNote2 (for table2), RandomNote3 (for table3).

Thx you so much this is exactly the thing I want