Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
daxesh
Helper I
Helper I

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

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.

2 ACCEPTED SOLUTIONS

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!

 

View solution in original post

Thank you so much @vinaypugalia.

It worked for me.

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

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.

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.