Forum Discussion

Ralpho93's avatar
Ralpho93
Regular Visitor
2 years ago

New Table in Table View

I'm not a DAX expert and searched my way through the forums and tried Chat GPT as well, but couldn't find a solution to my problem, so hopefully the community can help. 

 

I have a dataset with 4 tables (table A. table B, table C, table D). I want to create a new table with the following requirements: 

  • contains all data from table A
  • contains a subset of table A as an append based on a filter in table A, so basically I want to duplicate a part of table A
    • if possible, values in one column in this appended subset should be replaced by values in table B using a lookup
    • otherwise I would just add (instead of replace) the additional lookup column from table B and use this a basis for further calculation
  • contains a new columns from table C based on a lookup
  • contains all data from table D as an append

I could quite easily create this logic in the query editor, but as table A has more than 3M rows, this blows up the file and makes it super slow, so my hope is to to this in the table view using DAX. 

 

Hope someone can help me with this!

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Yes, you can use SELECTCOLUMNS, UNION, and (if needed) DISTINCT  to cobble that together in DAX.

     

    Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
    If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Ralpho93 ,

     

    I created some data:

    Sort the ABCD table sequentially from left to right

    Here are the steps you can follow：

    1. Create calculated table.

    True =
var _table1=
FILTER(
    'TableA','TableA'[Group] in {"B","C"})
var _table2=
SUMMARIZE(
    _table1,[ID],[Group],"Value",
    IF([ID] in SELECTCOLUMNS('TableB',"id",'TableB'[ID])&&[Group] in SELECTCOLUMNS('TableB',"group",'TableB'[Group]),
    SUMX(
        FILTER(ALL(TableB),
        'TableB'[ID]=EARLIER([ID])&&'TableB'[Group]=EARLIER([Group])),'TableB'[Value]),
    SUMX(
        FILTER(ALL(TableA),
        'TableA'[ID]=EARLIER([ID])&&'TableA'[Group]=EARLIER([Group])),'TableA'[Value]))) //2
var _table3=
SUMMARIZE(_table2,[ID],[Group],"Value",
    IF([ID] in SELECTCOLUMNS('TableC',"id",'TableC'[ID])&&[Group] in SELECTCOLUMNS('TableC',"group",'TableC'[Group]),
    SUMX(
        FILTER(ALL('TableC'),
        'TableC'[ID]=EARLIER([ID])&&'TableC'[Group]=EARLIER([Group])),'TableC'[Value]),
    SUMX(
        FILTER(_table2,
        [ID]=EARLIER([ID])&&[Group]=EARLIER([Group])),[Value])))
return
UNION(
    _table3,'TableD')

    2. Result:

     

    If it doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

     

    Best Regards,

    Liu Yang

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

    • Ralpho93's avatar
      Ralpho93
      Regular Visitor
      2 years ago

      Hi Anonymous 

       

      First of all thanks for the effort! I put together a simple file containing dummy data that should explain exactly what is needed, hope this brings more clarity.o

       

      Comment on initial request: I took out the column that should be added from another table using lookup as it's not relevant anymore.

       

      Sample File