How to divide semantic model for specific range of dates from OneLake ?

[jaryszek]

Hello,

How to divide semantic model for specific range of dates from OnaLake ?

In OneLake I have a full data set in parquet tables.

I want to know divide it for specific semantic models for a data ranges like in 1 i want to have only 7 days, in second full month. 
How to make this working? 

And second question. Relationships are created in semantic model. How to restrict it? 

Best,
Jacek

[jaryszek]

Thanks but i do not want to use sql endpoint at all. 

[Natalie_iTalent]

Hello @jaryszek,

The comment you responded to has been removed.  I hope you are able to get your question answered.

Best,

Natalie H.

Community Manager 

[jaryszek]

Nope, nobody answered. 

[v-pgoloju]

hi @jaryszek,

Thank you for reaching out to the Microsoft Fabric Forum Community.

You have a full dataset in Parquet format, and you want to create separate models for Last 7 days, Full month

Here’s how you can do it: Use Notebooks or Dataflows Gen2

Notebooks let you write simple Python or Spark code to filter your data by date.

Dataflows Gen2 let you use a visual interface (like Power Query) to do the same thing without writing code.

Once filtered, Save each filtered dataset as a new table in your Lakehouse.Then, build a separate semantic model in Power BI for each table.

This way, you’ll have: One model showing just the last 7 days, another showing the full month and so on.


You mentioned that relationships are already created in your semantic model, but you want to restrict or control them

Use Role-Level Security (RLS)

In Power BI, you can define roles that control what data users see.

You can use DAX filters to limit data based on dates or other fields.

Example: A role that only sees data from the last 7 days.

Use Separate Models

Since you’re already splitting the data by date, each semantic model can have its own relationships.

This avoids the need to dynamically restrict relationships.


Manual Relationship Control

In Power BI Desktop, turn off Auto Detect Relationships.

Manually define only the relationships you want.

This gives you full control over how tables connect.

Thanks & Regards,

Prasanna Kumar

[jaryszek]

Thank you.

"build a separate semantic model in Power BI for each table"

Yes, this is a good direction. 
Assuming I have the same baseline model with the same schema and only want to change data granaularity within model, how can i achive that? 

Imagine user wants to compare year 2024 on Months but after that he wants to compare last 7 days with 2 weeks ago. I have different granularity for it so it means i have data in different semantic models. 

How I can get proper data based on user slicer selections? How to change attached semantic models? How to read them ?

Best,
Jacek

[Praful_Potphode]

Hi @jaryszek ,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Since the question is on dividing the data into multiple semantic models.you can do that in 2 steps.

• step1 create a fabric notebook-in this step we will load the parquet data into dataframe using abfs path or relative path.then you can apply different filters to it and create multiple tables based on your requirements.once the tables are created save every table as a lakehouse table.
• step 2 go to powerbi desktop,select get data and lakehouse connector after that select the tables you want in your model.once loaded publish them.(this is one time activity post that your models will be ready and you can schedule refresh).

This is how you can solve your issue.

for relationship issue avoid giving same names to the matching columns to create relationships automatically(for example,if one table has empid,name other column as id.power bi will auto create relationships if column names are same).

[jaryszek]

Ok what if i have 1 semantic model and want to change different date granularity? 

How to model star schema to achive it? 

And multiple semantic models --> i do not know if this is the best option to show different granularities based on the same report.

[Praful_Potphode · Accepted solution]

Hi @jaryszek 

Thanks for reaching out again.

in this case we can create self service BI Model with RLS/OLS as mentioned by v-pgoloju.

the data model may look like below:

• there will be one fact table which will have data at day level and another fact table at year/month level(based on your requirement ).
• Date dimension will be connected at day granularity to day fact table
• Date dimension will be connected at year/month granularity to year/month fact table.
• Other dimensions will be connected based on keys.

then you can design RLS/OLS.(in OLS you can create a role who will see day level fact table and another role who will see year/month level fact table)

Thanks & Regards,

Praful Potphode