Forum Discussion
Advocate Vmodelling new table edit
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.
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"
Advocate VHi 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 :-)