Forum Discussion

DavidMoss's avatar
DavidMoss
Advocate V
10 years ago
Solved

modelling new table edit

In PBI Desktop under modelling there is the option to create "New Table" using DAX calculated table functions. 

What are our editing/formatting options of that new table in the PBI UX ? (Apart from the obvious advanced DAX formulas)

I was just trying to create a dimension on the fly using this formula in the formulas bar of the new table option 

 

dimensionTable = distinct ( Facttable [dimensionColumn] )

 

Used to the good features in the edit query UX what can we do with these new tables added in this way as they do not appear as new queries in the Query Editor ?

 

 

 

 

Need Help
  • DavidMoss's avatar
    DavidMoss
    10 years ago

    In the end i resolved to using some simpe DAX. Heres what i did.

     

    1: So the original dimension table i extracted from my fact table as discussed above using Calculated table 'New Table' feature :
    dimensionTable = distinct ( Facttable [dimensionColumn] )

     

    2: Then in that new table i created a caluclated column to return a 1 if the value is 'null' or 'blank' inthe dimensionColumn

    NullTest = IF('dimensionTable'[dimensionColumn]="",1,2)

    And i used this table as a staging table for step 3

     

    3: I created a further new calculated table the final dim_Dimension table to use in the datamodel with distinct values for the end client to slice fact table. I used once again the 'New Table' option using the dax;

    dim_Dimension = FILTER('dimensionTable', 'dimensionTable'[NullTest] = 2)

     

    It seems a bit of an excessive work around, but at least now i have dynamic on the fly dimensions in  the model.

     

    PS some may say why didn't i just report off the original dimension from the fact table...well i don't like end client interacting with master fact table, in fact i like to keep all my dimensions seperate in a Kimball model architecture and all my measures seperate aswell.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    I don't believe there is any way to round trip the newly created table into power query (the query editor).  Your choices are to "create the new table directly in power query... instead of via dax" or "create calculated columns via dax to add new columns to the calculated table"

     

    • DavidMoss's avatar
      DavidMoss
      Advocate V
      10 years ago

      Hi Anonymous thanks for your consideration but i was looking to understand if anyone had ways of enhancing the calculated tables created via the New Table feature. More interestingly to understand strategically what can be done to enhance my datamodel that maybe the community doesn't know about with this feature.

      Please we are not looking for generic basic how to use Power BI advise :-)

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        I have re-read your original question, and I'm not sure what you are asking.  I can confirm that you can't edit tables created via the New Table funtionlity in the Query Editor dialog.  But as far as "what can you do w/ the new table" -- well, anything you want/need?  Add calculated columns, write measures against them, create relationships etc.   A common usage would be... if I don't have a StoreMaster table, but have lots of Sales... it would make sense to use the New Table functionality to create a store master table and relate back to the Sales table.

         

        But like I said -- your question isn't super clear to me.