Ibrahim Mondal’s Post

Hi Data Folks, #day10 Today's topics are the Composite model from Power BI and the Subquery from SQL. So, let's begin. 📌Power BI Q. What is the Composite model? A model having a combination of the tables with import mode connection as well as direct query/live query connection mode is a composite model. 📌SQL Q. What is the subquery? A subquery is a query nested within another query. It can be used to retrieve data from one table based on values from another table or perform complex calculations. Thank you! #powerbi #powerbideveloper #dataanalytics #dataanalysis #data

#Important #Questions #PowerBI #SQL 1. Tell me about yourself? 2. How would you rate yourself in Power BI & SQL out of 10? 3. Are you aware of feature called RLS? 4. Is it possible to use RLS to limit the columns & measures? 5. What are the connectivity modes available in Power BI? 6. In Direct query can you use RLS features? 7. How Power BI get to know that all those data related information when we use direct query connectivity mode? 8. Are you aware of metadata? 9. List out some of the common table DAX functions? 10. Scenario: There is a data table having more than 4TB of data, you have used live connection or direct query mode. You imported he data and then after that you created a chart. When you are selecting any filter. For refreshing the whole chart, it will take 6 minutes of time and you wanted to reduce to 1 min then client ask you to reduce to 1 min. They told to optimise what are all the procedures you will take to optimise it and to reduce to 1sec 11. What is bidirectional cross filtering? 12. Different kind of filters have in Power BI? 13. Difference between Primary key & unique key? 14. Difference between UNION & UNIONALL? 15. Explain the types of joins with an example? 16. Can we put any condition in cross join? 17. Use cases of full outer join? 18. Can we use left join as a inner join?

Measuring the total time spent querying sources in Power BI DirectQuery mode https://lnkd.in/e4_GxWwp

Hello, Power BI enthusiast! 📊 Let’s understand NaturalInnerJoin DAX in three steps. ➖Step-1: Create a relationship between both tables. ➖Step-2: Go to the Modeling tab > click on create a new table icon. ➖Step-3: Write a DAX function for inner join. InnerJoin = NATURALINNERJOIN('Product', 'Price') ➖Common Usage note for function: 1- Columns being joined on must have the same data type in both tables. 2- These functions does not support Direct Query mode. 3- The names of the columns that define the relationship need to be different. #dax #powerbi #join #sql #dataanalyst #dataanalytics #powerbideveloper

Power BI: Import Mode vs. Direct Query – What’s Best for Your Data? In Power BI, choosing between Import Mode and Direct Query can impact the performance and flexibility of your reports. Here's a quick comparison: Import Mode: - Data is loaded into Power BI, which makes it fast for large datasets. - Great for high performance since queries run against the in-memory data. - You can access your data offline. - Limited refresh frequency (8 times a day, or 48 with Premium). Direct Query: - Data remains in the source, providing real-time insights. - No data size limits, suitable for very large datasets. - Performance depends on the source system, so it may be slower. - Requires a live connection to the data source at all times. When to use Import Mode: - When data changes infrequently. - When you need better performance for large, complex reports. - When offline access to reports is important. When to use Direct Query: - When you need real-time data. - When dealing with huge datasets beyond Power BI’s memory limits. - When data security policies require data to remain at the source. What’s your preferred mode? Share your thoughts in the comments! #PowerBI #DataAnalytics #ImportMode #DirectQuery #BusinessIntelligence

𝐃𝐢𝐬𝐭𝐢𝐧𝐜𝐭 Vs 𝐔𝐧𝐢𝐪𝐮𝐞 values in Power Query In power query window we have option to preview the data before loading it into the Power BI Three main data previews options are Column Distribution, Column Quality and Column Profile We will discuss about Column Distribution option today Column Distribution option allows to see metrics like Distinct and Unique values present it the data Now the question is what's the difference between Distinct and Unique 𝐃𝐢𝐬𝐭𝐢𝐧𝐜𝐭 𝐕𝐚𝐥𝐮𝐞𝐬 is the count of all different values within a column. This count includes all occurrences, even if a value appears multiple times 𝐔𝐧𝐢𝐪𝐮𝐞 𝐕𝐚𝐥𝐮𝐞𝐬 is the count of values that occur only once within the column As you can see in the image below 𝐒𝐚𝐥𝐞𝐬𝐓𝐞𝐫𝐫𝐢𝐭𝐨𝐫𝐲𝐊𝐞𝐲 column has 10 distinct and 10 Unique values It means that no duplicate value present in 𝐒𝐚𝐥𝐞𝐬𝐓𝐞𝐫𝐫𝐢𝐭𝐨𝐫𝐲𝐊𝐞𝐲 column and, if you check the 𝐂𝐨𝐮𝐧𝐭𝐫𝐲 column, it has 6 distinct and 5 Unique values 6 𝐃𝐢𝐬𝐭𝐢𝐧𝐜𝐭 𝐕𝐚𝐥𝐮𝐞𝐬 are United States Canada France Germany Australia United Kingdom, 𝐂𝐨𝐮𝐧𝐭𝐫𝐲 column have total 6 different values 5 𝐔𝐧𝐢𝐪𝐮𝐞 𝐯𝐚𝐥𝐮𝐞𝐬 are Canada France Germany Australia United Kingdom. They occurred only once.  #PowerBI #DataAnalysis #BusinessIntelligence #DataVisualization #PowerQuery #DistinctValues #UniqueValues #DataTransformation #DataQuality

⏩sharing some Power BI knowledge with you all, ever wondered how to import data into Power BI from SQL Server? 🔴 𝗜𝗺𝗽𝗼𝗿𝘁𝗶𝗻𝗴 𝗱𝗮𝘁𝗮 𝗶𝗻𝘁𝗼 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗳𝗿𝗼𝗺 𝗦𝗤𝗟 𝘀𝗲𝗿𝘃𝗲𝗿. ✔️ 𝗦𝗲𝗿𝘃𝗲𝗿 𝗡𝗮𝗺𝗲: Start by telling Power BI where your data lives, just provide the Server Name of your SQL Server. ✔️ 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 (𝗢𝗽𝘁𝗶𝗼𝗻𝗮𝗹): If we have multiple databases, we can specify which one we want to connect. No pressure if we are not sure😊 it's optional. ✔️ 𝗗𝗮𝘁𝗮 𝗖𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻: We can go with Import Mode to bring data into Power BI, or Direct Query to query it directly without importing. ✔️ 𝗦𝗤𝗟 𝗦𝘁𝗮𝘁𝗲𝗺𝗲𝗻𝘁 (𝗢𝗽𝘁𝗶𝗼𝗻𝗮𝗹): To obtain data for a specific period or condition, SQL statements are utilized to filter the data precisely according to our requirements. #PowerBI #DataAnalytics #DataAnalysis #DataConnection #SQLServer #DataVisualization #DataIntegration #BusinessIntelligence #DataDriven #DataPreparation

DAX Query View the fourth view is right now in preview mode in the Power BI Desktop but useful for those who write lot of DAX(Data Analysis Expression). It solves lot of problem and reduces time and effort. It allows us to do the following. 1# Create, debug, and see the output of more than one measures at the same time no need to wait for individual measure. 2# Make changes to multiple measures at once instead of changing measures one by one. 3# If you have used one measure inside another and want to see the logic behind the nested measure you can see the logic without opening the nested measure manually, just hover the mouse pointer over the nested measure and see the formula behind. 4# If you have to write multiple measures of same logic with only 1 or 2 words difference DAX Query View has the ability to replace words in Power BI instead of replacement in word file. 5# Limit the number of rows in a result set. #powerbi #dax #businessintelligence #etl #dataanalytics #powerquery #daxqueryview #datamodeling #dataengineering #sql

Power BI Data Import Modes: Unlocking the Right Approach for Your Business Intelligence Needs As a data professional, understanding the nuances of Power BI's data import modes is crucial for optimizing performance and functionality. Let's explore the key differences: 1️⃣ Import Mode Full data import into Power BI Blazing-fast analysis performance Enables offline work Ideal for smaller to medium-sized datasets 2️⃣ DirectQuery Mode Data stays at the source Real-time querying for up-to-date insights Perfect for large datasets Slightly slower than Import mode 3️⃣ Live Connection Tailored for SQL Server Analysis Services or Power BI datasets Leverages existing models and calculations Queries processed at the source 4️⃣ Composite Mode Best of both worlds: combines Import and DirectQuery Optimizes performance while maintaining data freshness Flexible approach for complex scenarios 5️⃣ Dual Mode Dynamic switching between Import and DirectQuery Adapts based on specific query requirements Choosing the right mode can significantly impact your BI solution's efficiency and effectiveness. Which mode aligns best with your current projects? #PowerBI #DataAnalysis #BusinessIntelligence #DataStrategy

For data pros: Speed up your work. Discover Power Query shortcuts! ⬇ Examples of Optimization Techniques 1️⃣ Minimize Data Load Only load essential columns and rows. For instance, if you're analyzing sales data, focus on relevant date ranges and specific product categories to avoid unnecessary data clutter. 2️⃣ Use Native Query Options Whenever possible, leverage Power Query’s ability to push operations back to the data source (like SQL databases). This reduces the data processing load on Power BI. 3️⃣ Avoid Unnecessary Calculations Perform calculations in the data source if possible. This means fewer transformations in Power Query and a lighter load for Power BI. 4️⃣ Merge Queries Judiciously While merging queries is powerful, it can be resource-intensive. Ensure you're merging only when necessary and with optimized join conditions. 5️⃣ Utilize Index Columns for Large Tables When working with large datasets, creating an index column can improve sorting and filtering efficiency. 6️⃣ Optimize Data Refresh Schedules Schedule data refreshes during off-peak hours to ensure efficient use of resources and reduce the impact on end-user experience. By implementing these techniques, you'll see a marked improvement in your Power BI reports' load times and overall performance! #PowerBI #PowerQuery #DataOptimization #DataAnalysis #BusinessIntelligence