Forum Discussion
SSAS > Tabular Model > How to generate one table from another table?
Hello Daxesh,
To address your concern, basically what I can recommend is, just have 1 fact table with all the required columns & then keep adding expression columns with the required filter conditions. If the condition is true, return true/1 else false/0. Then on the visualisation front, just add page level filter using the expression column related to this view & set it to TRUE/1.
Doing this way, you have can use the same fact data filtered based on multiple conditions for the different views you want to generate. And the visualisation will consider only the rows having TURE/1 in their corresponding expression column.
Hope, this helps!
Thank you so much vinaypugalia.
It worked for me.
Community ChampionIn this case I wouldn't recommend to build one table "from antother (query) table" in Power BI.
It seems to be an unfiltered fact table that you want to filter: Just adress the fact table in your SSAS-model, choose the needed fields and filter the returned table in the query editor. These filters will be folded back to the server, meaning that only the reduced amount of rows will be returned to PBI. This normally gives best performance.
Actually, I need 7 to 10 filtered fact tables based on different criteria & this is very much possible that a row qualify the criteria in more than 1 table. Hence, data repeatition is what I am expecting.
My concern here is, if I go ahead & get this data from the source SQL tables into my model, everytime I process the model, it will take more time to refresh as the data being transfered from the source to the SSAS data model will increase.
Please guide if my concern is incorrect and if it's correct what could be done to handle this.
Thanks!
- vinaypugalia
Resolver I
Hello Daxesh,
To address your concern, basically what I can recommend is, just have 1 fact table with all the required columns & then keep adding expression columns with the required filter conditions. If the condition is true, return true/1 else false/0. Then on the visualisation front, just add page level filter using the expression column related to this view & set it to TRUE/1.
Doing this way, you have can use the same fact data filtered based on multiple conditions for the different views you want to generate. And the visualisation will consider only the rows having TURE/1 in their corresponding expression column.
Hope, this helps!
Thank you so much vinaypugalia.
It worked for me.
- ImkeF
Data-modelling is very complex and I don't feel capable to give a recommendation based on the information given so far. But in general I don't see the benefit in splitting up the fact-table with filters in the data-model.
The filtering should normally be done through dimension attributes and measures created accordingly.