SSAS > Tabular Model > How to generate one table from another table?

[daxesh]

Hello,

I am stuck in a Power BI and SSAS model implementation.
I am new at preparing Tabular Model using SSAS. I plan to use this model in Power BI.

My problem is that I am not able to generate one table from another table.
Actually, I am planning to generate a new table from a fact table after applying some conditions in my model.

Let’s say I have fact table fact_data and I want to generate fact_data_1 applying condition on fact_data (like ‘fact_data’[status]=”Active”).

Further, I will use both the tables in 2 separate visualizations respectively.

Please guide how this could be achieved.

[vinaypugalia · Accepted solution]

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!

[ImkeF]

In 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.

[daxesh]

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 · Accepted solution]

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!

[daxesh · Accepted solution]

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.