Forum Discussion
modelling 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.
let me re-phrase the observation i started with:
"What are our editing/formatting options of that new table in the PBI UX ? (Apart from the obvious advanced DAX formulas)."
I'm happy to see you agree (A): 'that you can't edit tables created via the New Table funtionlity in the Query Editor dialog'. OK so that is eliminated.
We both know we can (B) enhance the table with DAX be it calculated columns etc....
Assuming A&B then more accuratley my question is :
How can we edit the data in the new calculated table ? It appears this is not possible.
When i say edit i mean to execute similar features that are in the Power BI Query Editor.
I am asking this question because when i created the caluclated table dimension i ended up with both 'blank' rows and 'Null' rows which when trying to use this table as a dimension in the datamodel it was not possible to use it as PBI sees blank and null as the same value hence the dimension is no longer 'Distinct' or unique.
So what i want to do is like what i can do in the Query Editor edit/ change /replace tumple values.
I hope that is clearer. ?
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.