How to create model using Snowflake data tables

[Gopal_PV]

Hi Friends,

I have above tables with different other columns due space constraint i have taken main column from the tables.

This is in Snowflake . I have to load these tables or views from snowflake. There are  few conditions need to apply

when chargeType 55 then SoftwareChargeFee

when chargeType 66 then Setupfee   Like condition based charge fees are there.

What is the best way to apply those conditions?

I am building this model with no reference.

Can you please help what is the best way to created model

1. Should go with creating views with business rules and load to PBI?

2. Should i load all the tables to power bi then apply business rules?
3. Should i created individual for individual tables and Load them?
4. What is the best way to create DateTable or Should i created in the individual tables with Hierachy . What best use case to date table.

Please help me in this design

Which is the best mode  Import/ Direct/ Composite

[v-ssriganesh]

Hello @Gopal_PV,

Hope everything’s going great on your end. Just checking in has the issue been resolved, or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Gopal_PV,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your model in Power BI Desktop using sample data based on the table structure and logic you shared. Specifically, I applied your condition:

• When ChargeType = 55, it's labeled as SoftwareChargeFee
• When ChargeType = 66, it's labeled as SetupFee

The data model includes relationships between Charges_By_Account and Accounts_Account, and I've created a calculated column to classify the charges based on ChargeType.

Best practice start with Import mode for faster development and testing; switch to DirectQuery or Composite later if your data grows or requires real-time updates from Snowflake.

Please find the attached .pbix file for your reference. You can open it in Power BI Desktop to explore the data, model, and visuals.

Thank you, @wardy912 & @amitchandak for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[wardy912]

Hi @Gopal_PV 

I suggest:

• Snowflake Views (with business rules)
• Power BI (Import Mode)
• Power BI Model (Fact + Dimensions + Date Table)

Where possible, it's always best to add logic before importing to BI, as this will improve performance of your BI model if it doesn't need to calculate the logic. This also provides a centralised location for collaboration etc.

Use import mode to also get the best performance.

Import	Most common	Fast performance	Data not real-time
DirectQuery	Real-time needs, large datasets	Up-to-date data	Slower performance, limited DAX
Composite	Mix of both	Flexibility	Complexity in model design

If tables are independent, load them separately.
If they are related by keys (e.g., customer ID, transaction ID), create a fact view and dimension views in Snowflake.

Create the date table within Power BI, I always use the following (point it to your most reliable table for a dynamic date)

Date = 
ADDCOLUMNS (
    CALENDAR (
        MIN ( [Date column from existing table] ),
        MAX ( [Date column from existing table] )
    ),
    "MonthNo", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "MonthYear", FORMAT ( [Date], "MMMM YYYY" ),
    "MonthYearShort", FORMAT ( [Date], "MMM YY" ),
    "MonthYearNo", FORMAT ( [Date], "YYYYMM" ),
    "Quarter", QUARTER ( [Date] ),
    "Year", YEAR ( [Date] ),
    "Day", DAY ( [Date] ),
    "WeekNumber", WEEKNUM ( [Date] ),
    "WeekdayNum", WEEKDAY ( [Date] ),
    "WeekdayName", FORMAT ( [Date], "DDDD" ),
    "PreviousWeek", WEEKNUM ( [Date] ) -1 ,
    "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1),
    "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7),
    "YearMonth", FORMAT ( [Date], "YYYY-M" ),
    "Financial Year", IF (MONTH ([Date]) >= 1 && MONTH ([Date]) <= 12, YEAR ([Date]), YEAR ([Date]) + 1)
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[amitchandak · Accepted solution]

@Gopal_PV , Import mode is best. 
For this, you need to createa  calculated column in Power Query or DAX, this formula  might not work well with Direct query mode (as column) 

when chargeType 55 then SoftwareChargeFee

when chargeType 66 then Setupfee

PQ
NewColumn =
SWITCH(
TRUE(),
TableName[chargeType] = 55, "SoftwareChargeFee",
TableName[chargeType] = 66, "SetupFee",
BLANK()
)

DAX
= if [chargeType] = 55 then "SoftwareChargeFee"
else if [chargeType] = 66 then "SetupFee"
else null




Others 

Can you please help what is the best way to created model

1. Should go with creating views with business rules and load to PBI?

In Direct query do at source, Import you can have in PBI 

2. Should i load all the tables to power bi then apply business rules?

Yes, in Import mode , prefer import mode 

3. Should i created individual for individual tables and Load them?

You can load views or table. prefer star schema