Forum Discussion

juncco888's avatar
juncco888
Advocate I
5 years ago
Solved

Pass a value from one table to select that value from multiple columns in another table

Hi there, I am new to Power Bi and DAX and am having difficulty selecting and displaying records based on a single value that exists in multiple columns in my data set.  I want to select a value for customerid from one table and then select all records from another table that contain the value in any of the three columns and then display the records based only on one of the three columns.

For example.  Get all records that contain the value “1” in the Soldto or EndCust or ShipTo columns then be able to display all records either by Soldto with Value 1 or EndCust with Value1 or ShipTo with value1 as well as show all records that contain the value in any of the three columns

I have two tables:  Customerid and Sales.  The customer ID table contains the list of values which will be used to filter the second table. Only one value will be selected at a time.   

I used shipTo as the many relationship but I could have also used SoldTo or EndCust fields as the values from Customerid will match to any of these.  

View of visuals with nothing selected

View of data with Customer ID of 1 (note this only includes records that have 1 in the shipto column due to the relationship between customerid and sales).

Output again  (this does not show the record that has a value of 1 in endcust.  Again this makes sense based on the relationship.)

 

The sales data does not contain a CustomerID column and the three fields that do have the values may not have all of the id numbers in them.

The actual dataset I will be working with is huge so I don’t want to create several different tables if it can be avoided.   Any suggestions for how I can accomplish selecting a value for customerid from one table and then select all records from another table that contains the value in any of the three columns and then display the records based only on one of the three columns, or all three, and combinations thereof.

Any assitance would be greatly appreciated.   Thanks  

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    juncco888 In this case you don't want/need ANY relationship between the CustomerID table and Sales table. 

     

    Create a measure: 

    Selected Customer Sales =

    SUMX(

    FILTER(Sales,

    Sales[ShipTo] IN VALUES(CustID[CustID])

    || Sales[SoldTo] IN VALUES(CustID[CustID])

    || Sales[EndCust] IN VALUES(CustID[CustID])

    ), Sales[Amount])

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    juncco888 In this case you don't want/need ANY relationship between the CustomerID table and Sales table. 

     

    Create a measure: 

    Selected Customer Sales =

    SUMX(

    FILTER(Sales,

    Sales[ShipTo] IN VALUES(CustID[CustID])

    || Sales[SoldTo] IN VALUES(CustID[CustID])

    || Sales[EndCust] IN VALUES(CustID[CustID])

    ), Sales[Amount])