Question 1
Difference between Calculated Column and Measures and how they are helpful while creating report?
Calculated columns works on row context, which means it performs the calculation row by row, while Measures operate on filter context, which means they offer the result on the flyer, which means whatever filters are used, the result will be generated.
The Calculated column takes up space in the model, but the Measures do not. The calculated column is physically present, which means you can establish relationships with this column in the data model. However, since measures do not exist, we cannot create any type of relationship with them.
Question 2
How will you improve your power bi reports performance?
Our first step is to verify that all columns in the query-loaded tables have the correct data types. Incorrectly assigning columns is because their data type creates a lot of space in the model.
Second, remove unneeded columns from tables. Performance will improve if you eliminate columns
that are not used in any report visuals. We can reduce the amount of rows that you’re pulling from the Source alright with the assistance of aggregation or by reducing the number of rows from the Source itself. Unselect the auto-generate date and time calendar in file and settings. Unless unchecked,
power bi generates a date table for each year, quarter, and month, which takes up model space and reduces performance.
Question 3
How many types of relations are there we can perform while creating data model in PBI?
One-to-one, many-to-one, and many-to-many relationships
One-to-one connections mean both columns have unique values. Due to redundant data, this rare cardinality class typically indicates a faulty model design.
Builders should choose a one-to-many or many-to-one link that matches the column order. Consider how to link Product Sales using each database’s Product ID field. The Product table has one-to-many cardinality since the Product ID column contains unique values. Reversing Sales to Product yields
many-to-one cardinality.
A many-to-many relationship suggests redundant data in both columns. This cardinality is rare. It helps with complex model creation. It may relate many-to-many or high-grain facts. Product category data stores sales target information, whereas product dimension data stores product data.
Question 4
What are the types of filtering you have used in your report?
So we can speak about visual level filters, page level filters, report level filters, drill through filters, and book markings if you have used book marks in any sort of filtering, so all of these alternatives you can discuss in filtering.
Question 5
If you’re calculating total sales and you want to apply filter function inside the calculate column how it will work?
So, the filter function is never used directly in any Dax code; it is always nested within a calculate function.
What does the filter function do?
It creates a subset of the table. As you all know, the second argument to the calculate function is the filter expression, so the filter function is used there. It will help the Dax code run faster if it is used within or next to the calculate functions.
Question 6
How many dashboard you have created till date and what are the objective of the dashboards?
So, depending on your experience, you should talk about at least five to six reports that you wrote from start to finish. If you can, try to tell the reviewer about the goals of all of those reports as well. This will make an effect on them.
Question 7
How will you decide that which are the metrics will be in dashboard to show these things to stakeholders without getting any requirement from stakeholders?
You need to know about the domain on which your report is based. To do this, you can Google it, learn about it, or watch some YouTube videos. Then, you can try to figure out what kinds of general KPI’s are used or terms that are specifically used in that domain. With those KPI’s and terms, you can go ahead and show the demo.
Question 8
What are bidirectional relationship, have you used them in your report?
Bidirectional relationship is when you set the direction in both directions, right cross filter Direction single or both. If you set it to both, you have a bidirectional relationship.
We shouldn’t use bidirectional filters very often because they slow down the report, but if you need to use them for some calculations, you can use cross filter Dax as a workaround.
Question 9
Difference between Star schema and snowflake schema?
In a star schema, the fact table is in the middle, and dimension tables are arranged around it in the shape of a star.
Dimension tables are linked to the fact table using a one-to- many relationship. That’s it for the star schema’s structure. It’s safer to use when 80% of the data is in the fact table. In the snowflake design, when your dimension tables are very large, it means that your master data is also very large.
In this case, we standardize the data by splitting the dimension tables into multiple dimension tables. This way, we have less duplicate data and the model runs faster.
Question 10
How many types of Securities are there in power Bi service?
With Power BI’s row-level security (RLS), you may restrict data access. You may set row-level filters inside roles to restrict data access. Power BI lets users view semantic models in a workspace. RLS restricts data viewing to Viewers. Not for Contributors, Members, or Admins. Static RLS: This form of RLS relies on static rules that remain constant throughout time. Static RLS may limit access to a department- specific report to department members. The regulations will stay unchanged in this situation.
Dynamic RLS: This RLS uses changing rules. Say you have a project report and a team working on
it. You may need to adjust the rules to restrict report access to existing team members when they change. Rules alter over time in this scenario.
Question 11
How many types or roles are available in power bi service and what was your role?
It’s mostly talking about the Power Bi admin role, the member role, the contributor role, or the viewer role.
As a Power BI developer, I usually had access the member level, which meant I could make
reports, publish them, make changes to them, and even add users. As a power BI developer, I also used to give users viewer-level access.
Question 12
Connecting different data sources with power bi especially Ms SQL (they are using microsoft’s ecosystem)as database and SharePoint as well?
This question shows that the interviewer wants to know how to combine data from different sources into a single Power BI report.
For example, if you’re using both a SQL Server database and SharePoint as a source, remember that if you imported the data from the database, you can also include other data sources, such as SharePoint. If the database connection was direct query connectivity, however, the SharePoint connection would not have been possible.
Question 13
If an Excel file contain multiple sheets how will you import all of them in power bi?
I think this question is asking how to pull all of the data from multiple CSV files at once. To answer that, you can say that you will put all of the files in a separate folder and then pull that folder into Power Query.
Once you have that folder in Power Query, you can click on “Combine Files” and all the data from all of the CSV files will be uploaded immediately into Power Query.
Question 14
Help me to take you through the process you follow and create dashboard from scratch how you start?
In real life, any project has one business analyst who works directly with the client and gathers the requirements. Once he understands the requirements, he puts them on a BRD and shares it with you as a power bi developer.
The business analyst also makes you meet with him. You will analyze those items using the BRD, then fetch the tables in power query, conduct all the necessary transformations, load the tables, and model them in the data model tab.
And do the visualization. If any calculations are needed using Dax, you will create measures and calculated columns in the BRD.
Finally, using the standard visuals in the standard pane, you will visualize the report and give the client a demo after a few days.