Forum Discussion

NMahi1703's avatar
NMahi1703
Frequent Visitor
3 years ago
Solved

Creating New Table by two tables and with filter conditions

Hi Team,

 

I need to create a table with two tables data by appyind the conditional filers as below.

Table1

Account IDID
36301811
76253291
76255567

Table2

Account NumberBilliDName
3630NAP1
3630NAP2
3630NAP3
3630NAP4
76253291P5
76253291P6
88883291P7
76254691P8
7625 P9
7625 P10
7625 P11

Above table has relationship as Table1 Account ID with Table2 of Account Number. 
Conditions to Filter Data:

1. Table1 ID is matched with Table2 BilliD then Account Number Product should map with BillId Name
2. If Table1 ID is not matched or found in Table3 BilliD then Name Rows Assoisated with Account Number Name's Should Display in Table.

OutPut:

IDName
1811P1
1811P2
1811P3
1811P4
3291P5
3291P6
5567P5
5567P6
5567P8
5567P9
5567P10
5567P11

Thanks

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below formula and the attached pbix file.

    New Table =
VAR _statustable =
    DATATABLE (
        "status", STRING,
        "index", INTEGER,
        {
            { "Critical", 1 },
            { "Warning", 2 },
            { "Normal", 3 }
        }
    )
VAR _resulttable =
    SUMMARIZE (
        GENERATE (
            Table1,
            FILTER (
                Table2,
                IF (
                    COUNTROWS ( FILTER ( Table2, Table2[BilliD] = Table1[ID] ) ) > 0,
                    Table2[BilliD] = Table1[ID]
                        && Table2[Account Number] = Table1[Account ID],
                    Table2[Account Number] = Table1[Account ID]
                )
            )
        ),
        Table1[ID],
        Table2[Name],
        Table2[Acc Status]
    )
RETURN
    ADDCOLUMNS (
        _resulttable,
        "@Status",
            MAXX (
                FILTER (
                    _statustable,
                    [index]
                        = MINX (
                            FILTER (
                                _statustable,
                                [status]
                                    IN SUMMARIZE (
                                        FILTER ( _resulttable, Table1[ID] = EARLIEST ( Table1[ID] ) ),
                                        Table2[Acc Status]
                                    )
                            ),
                            [index]
                        )
                ),
                [status]
            )
    )

     

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    New Table = 
SUMMARIZE (
    GENERATE (
        Table1,
        FILTER (
            Table2,
            IF (
                COUNTROWS ( FILTER ( Table2, Table2[BilliD] = Table1[ID] ) ) > 0,
                Table2[BilliD] = Table1[ID]
                    && Table2[Account Number] = Table1[Account ID],
                Table2[Account Number] = Table1[Account ID]
            )
        )
    ),
    Table1[ID],
    Table2[Name]
)

     

  • NMahi1703's avatar
    NMahi1703
    Frequent Visitor
    3 years ago

    Hi Jihwan_Kim ,

     

    Thanks for solution but still i'm stuck with another problem with your code.
    My Table2 has follwing Data

    Account NumberBilliDProduct NameVersionAcc Status
    3630NAP11Warning
    3630NAP22Normal
    3630NAP32Normal
    3630NAP410Normal
    76253291P52Warning
    76253291P69Warning
    88883291P74Critical
    76254691P82Warning
    7625 P96Normal
    7625 P107Critical
    7625 P118Normal

    Here I have each Account Status Column Value may be Normal , Warning , Critical. I want to create a Staus Column as  highiest  Status value realted to each ID
    Status Order is Critical , Warning , Normal.
    Desired Outupt is 

    IDNameStatusAcc Status
    1811P1WarningWarning
    1811P2WarningNormal
    1811P3WarningNormal
    1811P4WarningNormal
    3291P5WarningWarning
    3291P6WarningWarning
    5567P5CriticalWarning
    5567P6CriticalWarning
    5567P8CriticalWarning
    5567P9CriticalNormal
    5567P10CriticalCritical
    5567P11CriticalNormal

    Thanks.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      Please check the below formula and the attached pbix file.

      New Table =
VAR _statustable =
    DATATABLE (
        "status", STRING,
        "index", INTEGER,
        {
            { "Critical", 1 },
            { "Warning", 2 },
            { "Normal", 3 }
        }
    )
VAR _resulttable =
    SUMMARIZE (
        GENERATE (
            Table1,
            FILTER (
                Table2,
                IF (
                    COUNTROWS ( FILTER ( Table2, Table2[BilliD] = Table1[ID] ) ) > 0,
                    Table2[BilliD] = Table1[ID]
                        && Table2[Account Number] = Table1[Account ID],
                    Table2[Account Number] = Table1[Account ID]
                )
            )
        ),
        Table1[ID],
        Table2[Name],
        Table2[Acc Status]
    )
RETURN
    ADDCOLUMNS (
        _resulttable,
        "@Status",
            MAXX (
                FILTER (
                    _statustable,
                    [index]
                        = MINX (
                            FILTER (
                                _statustable,
                                [status]
                                    IN SUMMARIZE (
                                        FILTER ( _resulttable, Table1[ID] = EARLIEST ( Table1[ID] ) ),
                                        Table2[Acc Status]
                                    )
                            ),
                            [index]
                        )
                ),
                [status]
            )
    )

       

       

      • NMahi1703's avatar
        NMahi1703
        Frequent Visitor
        3 years ago

        Hello Jihwan_Kim , 

        Your Solution is not working for if i have Mutiple Account Id's are assosiated with Same ID. Then it's not working kindly help me with solution
        Table 1: 

        Account IDID
        36301811
        76253291
        76255567
        36315567

        Table 2: 

        Account NumberBilliDProduct NameVersionAcc Status
        3630NAP11Warning
        3630NAP22Normal
        3630NAP32Normal
        3630NAP410Normal
        76253291P52Warning
        76253291P69Warning
        88883291P74Critical
        76254691P82Warning
        7625 P96Normal
        7625 P107Critical
        7625 P118Normal
        36315567P127Normal
        36315567P1310Normal
        3631 P145Critical

         

        Kindly Provide the Solution