Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Pulling a unique value from three tables

Jump to Best Answer
  • 1.  Pulling a unique value from three tables

    Silver Contributor
    Posted Sep 15, 2021 09:36 AM
    In Power BI-PBIX model I have pulled 3 tables (through SQL Server queries). Each table has two, or more columns. All three tables have one column in common, "Plan_ID". I am able to confirm, in Excel, that there is some overlap.
    How do I write a measure that looks at all three tables and pulls a unique Plan_ID, if it exists, in any one of the three tables?
    I've tried various types of UNION but, I'm getting everything.
    Here are the three tables;
    Plans Missing Lives (ClientID,PlanID,PlanTpe)
    Plans Missing Volume (ClientID,Missing Lives Volume,PlanID,PlanType)
    Plans Missing Rates (PlanID)
    Here is the DAX formula for creating a single table but I don't think it is correct.

    Tbl_All_Missing = DISTINCT(UNION(SELECTCOLUMNS('Plans Missing Lives',"Plan ID",'Plans Missing Lives'[PlanID]),
    SELECTCOLUMNS('Plans Missing Rates',"Plan ID",'Plans Missing Rates'[PlanID]),
    SELECTCOLUMNS('Plans Missing Volume',"Plan ID",'Plans Missing Volume'[PlanID])))

    Don

    ------------------------------
    Donald Fox
    BI Developer
    ------------------------------


  • 2.  RE: Pulling a unique value from three tables
    Best Answer

    Top Contributor
    Posted Sep 15, 2021 09:18 PM
    Hi Don:

    You can try for your first step:
    DISTINCT(
    UNION(ALL(Plans Missing Lives[PlanID]),
    ALL(Plans Missing Volume[PlanID]),
    ALL(Plans Missing Rates[PlanID])
    )
    )
    This should give you a combined list in a table of just PlanID.
    If you have a sample of this type of data it will be easier to answer/test. If you had either Plan Type or Client ID in the third table you could add those columns in.

    I hope this helps to start. 


    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 3.  RE: Pulling a unique value from three tables

    Silver Contributor
    Posted Sep 16, 2021 08:43 AM
    Hi William,
    That is what I needed. Thank you very much for your help!
    Don

    ------------------------------
    Donald Fox
    BI Developer
    ------------------------------



  • 4.  RE: Pulling a unique value from three tables

    Top Contributor
    Posted Sep 16, 2021 11:06 AM
    Your welcome. Glad it worked out!

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------