Explore topic-wise InterviewSolutions in .

These roles are basically DEALS for USER access, governance and security and below are roles:

• Office 365 Global Administrator: This role is Super ADMIN for Office 365 SUITE CALLED Global administrator and will be able to grant various admin roles to others
• Office 365 Billing Administrator: This role used to Manage licenses or subscriptions.
• Power BI Service Administrator: This role is inside of O365 suite and used to manage User metrics, users, Audit Logs, tenant settings and Premium settings. Only O365 Global Admin can assign this role to others to help out better control on Power BI space.
• Power BI Premium Capacity Administrator: This role can be assigned to others by Power BI Service Admin and used to assign Premium capacity to app workspaces.
• Power BI Embedded Capacity Administrator: This role is available in Microsoft Azure portal. O365 Global and Power BI Service administrators are called as Power BI Embedded Capacity Admin.

MICROSOFT has CATEGORIES Power BI users as below:

• Consumers (also called as End-Users) of Power BI who gets insights of the data in the form of reports and used for decision making.
• Designers who Collect, Wangle, Explore, Transform, Model, and Evaluate Data and then design reports, publish reports to Power BI Web SERVICE and create dashboards.
• Administrators who are responsible for User governance using Office 365 (like Provisioning of Power BI Access to workspaces Licenses) and Administering User SECURITY (like assigning Power BI admin roles and assigning Premium CAPACITY roles)
• Developers who helps Power BI consumers and designers to build integrating / embedding custom visuals in Power BI Reports and building streaming datasets.

Writing comments is very important that it helps others to understand it easily.

E.g. 

You have written 15-20 lines of complex DAX Code for Client ‘C1’ on the particular requirement and after few days you have switched to other Client ‘C2’.

Within few weeks after you joined C2, C1 clients reach you and needs code CHANGE in DAX which you earlier have given to them, however, a few weeks back, you were in full swing, wrote the code and you have not written single line comment in your DAX Code about to logic used or variables used or conditions used or functions used or columns used or measures used or tables used ETC. 

Now you have to again SPEND your time in parsing old code and you are completely lost in complicated code.

Finally, you will learn a lesson from the above Example that “Comments” are very important even if you want to understand your own code after a period of time you need to Read Comments.

Now in Power BI, we have Single line commenting and Multiline Commenting:

Comments are BASICALLY Green in colour.

• Single line commenting
  • We can use two Hyphens or Dashes or Minus sign to comment a single line

(OR)

• We can use two forward Slash to comment a single line
• Multi-line Commenting
  • We have to use forward slash followed by ASTERISK sign for beginning and to end asterisk sign then forward slash.

Logical Functions - These functions return information about values in the expression.

E.g. AND, OR, NOT, IF, IFERROR, SWITCH etc.

Math and Trig functions – These are like Excel's mathematical and trigonometric functions.

E.g. SUM, SUMX, ABS, CEILING, CURRENCY, EVEN, FLOOR, INT etc.

Date and time – These date and time functions are like in Microsoft Excel.

E.g. CALENDAR, CALENDARAUTO, DATE, DATEDIFF ETC etc.

Time-intelligence - These helps in calculations on calendars and dates.

E.g. SAMEPERIODLASTYEAR, TOTALMTD, TOTALQTD, TOTALYTD, DATEADD etc.

Filter - These returns specific data types, related tables for looking up values, and filter by related values.

E.g. CALCULATE, DISTINCT, FILTER, RELATED etc.

Information - These used on table or column to provide as a parameter to another function and TELLS exits or not by using TRUE or FALSE

E.g. ISERROR, CONTAINS, ISBLANK, LOOKUPVALUE etc.

Parent and Child functions - These help users to manage data that is shown as a parent/child hierarchy in the data models.

E.g. PATH, PATHCONTAINS, PATHITEM, PATHLENTH, PATHREVERSE.

Statistical- These functions perform aggregations and can ALSO filter a column before or create aggregations based on related tables.

E.g. AVERAGE, MIN, MAX, COUNT, MEDIAN etc.

Text- Can search for text within a string, or concatenate string values or return part of a string or formatting for dates, times, and numbers.

E.g. CONCATENATE, FORMAT, LEFT, RIGHT, TRIM etc.

Other functions – These are unique functions and cannot be defined by any above categories.

E.g. ERROR, EXCEPT, GROUPBY, SUMMARIZECOLUMNS etc.

Yes, this can be achieved.

• Need to INSERT Image using Page Background and wallpaper to give illusion to look similar web page.
• By using Bookmarks, Selection panes and Buttons.
• Bookmarks and Selection PANE to save with favorite VIEW name which want to be shown to consumers.
• Using Buttons can ASSIGN above bookmarks to actions.
• Buttons will be placed on Cover Page or Landing Page or Main page
• Except Main page all pages will be hidden state.
• Publish to Power BI Web Service.

YES, can be done.

• SELECT Clustered Column chart
• Drag Date hierarchy in Axis
• Drag NUMBERS Values
• In FORMAT options scroll  In X-Axis, Turn Off Concatenate labels option.
• Go to canvas, select Chart visual then click on more options, Select Sort A->Z
• Again, click on more options Sort by Date.

Yes, it’s possible using options below and can be ENABLED in the View menu.

• Bookmarks pane– to save default/preferred view and save with view name.
• Selection Pane – to SELECT REQUIRED visuals in a Page and rest can be hidden.

Process:
Initially, place all SLICERS on shape Rectangle > name Rectangle of List of slicers > go to selection pane then select rectangle and all the slicers and create Bookmark with name Select Slicer.

Finally, insert blank Button and name as “Show Slicers” in field properties Action ASSIGN Bookmark “Select Slicer”

STREAMING dataset is also called as Real Time Datasets and can create real time Dashboard, which updates just SIMILAR Live Cricket score or Live Football Score or Live Temperature etc.

• Open app.powerbi.com.
• upper right corner Click on + Icon.
• Select Steaming Dataset.
• Flyout will appear with 3 options API, Azure Stream Analytics & PUBNUB.
• Select API and hit next.
• Next flyout, Provide dataset NAME then Column names and respective data types.
• Enable Historic data analysis (which will help holding old data in dataset to perform analysis) then hit Create.
• Streaming dataset will be created, and it will show api url and also Codes – Raw, cURL & Powershell.
• Select PowerShell, copy code.
• Open PowerShell ISE.
• Paste the code and modify default values and press F5.
• It will immediately insert the data into Streaming dataset.

Go to dataset:

• check for the dataset which is created.
• click create REPORTS to insert visuals and PIN those visuals to Dashboard

(OR)

Go to dashboards:

• Upper middle click + Add tile.
• In flyout select source tile as REAL-TIME DATA then next.
• Your existing Steaming dataset will appear, if it’s not readily available then create new dataset.

• Open app.powerbi.com in favorite browser
• Click Upper right corner on settings.
• Select Power BI Admin.
• Select USERS and click Go to O365 Admin Center.
• Will be landed in portal.office.com.
• Select active users.
• Flyout will appear, check the REQUIRED users and edit product licenses.
• Select radio button “Add to EXISTING product license assignments" and ENABLE Power BI Pro License.
  (OR)
• Can use POWERSHELL Azure Active Directory command-lets to assign the license.

Variables are used to STORE values which allocate in Memory and in future which can be used in any kind of calculation.

By using variables, we can SIMPLIFY large (2-10 lines of) complex DAX formula into simple chunks which helps anyone easy to read and understand easily.

Variables help in prevent WRITING of the same expression again and again.

Syntax:

E.g. 

• To create calculated column where salary greater than 10,000 then Display as High,
• salary greater or equals to than 5,000 and less than or equals to 10,000 then Display as Average,
• salary less than 5,000 then Display as Low,

Let’s take DAX formula as complex form WITHOUT variables: 

Let’s take DAX formula using variables in chunks: 

The output will be the same for both without and with Variables, however with Variables easy to read and understand the DAX code.

One can create DAX Calculations or queries using formulas which can be used based on need basis and called them as

• Measures,
• Calculated Columns,
• Calculated tables,
• Row Filters (RLS - Dynamic Row-level Security).

Measures (OR Calculations OR Calculated Measures):

• Measures are calculations which are created logically (RAM) and are faster than calculated columns, which occupy the space in Power BI Desktop file (PBIX).
• Will not occupies space unless dragged and dropped in Visuals.
• These are created in DAX formula bar in Power BI Desktop.
• It is displayed with Calculator Icon
• Can also create & maintain individual “Key Measures” table and also folders in the to segregate like in below E.g.

• We must use functions like Count, Sum, Min, Max, etc. to call any COLUMN for creating measures.

E.g. Net Sales = SUMX(Sales, Sales[Quantity]*Sales[Price])

• We cannot use measures in Slicer as contains value and will change depending on Context.
• In the model, measures can also HIDE & unhide on need basis.

Calculated Columns:

• Calculated Columns are created physically and are slower than calculated measures.
• These are created in DAX formula bar in Power BI Desktop.
• We can REFER any column directly to create new column or derive column by using functions like Lookup(), concatenate(), format(),Sum() etc.…
• Below is example for creating column

E.g. Sales_Column = Sales_[Costs]*Sales_[Qty]

• We can use Calculated Columns in Slicer.
• Also, can create relationships between two or more tables.
• On a need basis, columns can hide & unhide in the model.

Calculated Tables:

• Calculated Tables can be created like Date Dimension (DimDate) or it can be derived based on existing tables in the model.
• These are created in DAX formula bar in Power BI Desktop.
• It also supports to establish relationships between other related tables.
• These can be hidden or unhide based on the requirement.
• Below is an example for creating Calendar table using DAX

E.g. DimDate = CALENDAR("01/01/2019","03/31/2019")

Row Filters (RLS – Dynamic Row-level Security):

• Row Filters also called as Dynamic Row-level Security.
• This is to filter the records based on Hierarchy.

e.g. You are General Manager of Sales for India and under you have Associate Managers which LOOKS after sales different states in India. So, the requirement is your Associate Managers should be able to see only respective states sales data (not others).

This requirement can be achieved by RLS and need to use a username () – for Names or userprincipalname () – for official Email ids DAX functions.

To create DAX FORMULAS, one should understand Context. Context is nothing but the result of the DAX formula will change based on Current Cell or Row for any related data.

Types of Context:

• Row Context (Multiple Row Context):

Current Row or a single line of record is called a Row context.
E.g. Table “Sales”

Here we MUST create Measure to derive the Total Sales i.e. Row by Row.

Hence we have to use SUMX() Function to calculate the Price X Quantity.

In SUMX ‘X’ mean Iterate. Row by Row data calculate/Evaluation

Total Sales = SUMX(‘Sales’,’Sales’[Price]*’Sales’[Quantity])

• WORKING Logic: 

First, go to Row 1 =Take Product of Price and Quantity as 3 * 1

Move to next Row 2= from row 1 result ‘3’, sum up with Product of Price and Quantity as 2*2 And so, on till it reaches the end of the table.

• Working in backend:

Row 1 = 3*1

Row 2= 3+(2*2)

Row 3 = 7+(1*3)

Total Sales = 10

Example Snippet for better understanding:

• Query Context:

As we all know that Context will change based on Current Cell or Row for any related data.

For example, consider Snippet below, you pull the Net Sales into Table or Matrix visual (Basically Tabular model), then according to Row and columns are chosen from Dimension table, Slicers selected or any kind of report filters applied Data will appear.

• FILTER context:

Filter context is simply is applying a filter on evaluation while creating Measure.

E.g. Below used Calculate() function which has the capability to change the context by applying filters.

Basically its evaluation Profit for Same Period Last Year using SamePeriodLastYear() function in Calculate.

FOUR types of operators are available in DAX Language to perform calculations:

To compare values then use Comparison Operators.

To perform arithmetic calculations on values, use Arithmetic Operators.

To join or concatenate two or more Text values then Concatenation operators.

To return SINGLE values based on True or False, use Logical operators.

There are two types

• Numeric types:
  • Whole Number (Integer (64 Bit)) - A 64-bit (eight-bytes) integer value
  • Decimal (Floating points) - A 64-bit (eight-bytes) real number
  • Currency (Money) - Currency data type allows values with four decimal digits of fixed precision.
  • Date (Date/TIME) - Dates and times in an accepted date-time representation.
  • Date – Represents just a Date (no time portion). 
  • Time – Represents just Time (no Date portion). 
  • Date/Time/TimeZone – Represents a UTC Date/Time. 
  • Duration – Represents a length of time.
  • Boolean - Either TRUE (1) (OR) FALSE (0).
• Other types:
  • Text (String) - A Unicode character data string. Can be strings, numbers or dates represented in a text format.
  • Binary Object – Images or any kind of binary large objects.
  • N/A or Blank - A blank is a data type in DAX that represents and replaces SQL nulls. You can use the BLANK function, and test for blanks by USING the logical function, ISBLANK.

It’s very important to understand the Logical Processing & execution in DAX and also to FORMAT using PRESSING (Shift+Enter) to moving it to next LINE and indented with spaces, unlike excel functions to WRITE in 1 line mixing all the formulas in the formula bar.

Explanation - DAX Logical Processing & execution starts from the innermost function i.e., IF() function – which is checking condition Country is equal to India by selecting distinct or unique values using VALUES() Function, parameters which is first part EXECUTES, then moves Filter() function to filter it by Country Function then finally moves Outermost SUMX Function to aggregate SalesAmount and multiply by 20% if its country is India and else with 10%.

DAX is the language of Power BI. It STANDS for Data Analysis Expressions. It is a functional language much like the Excel formulas.

Power BI PRODUCT team created DAX by mashing up “Excel formula Language”, “SQL Language” and “MDX Language” 

DAX is Simple, but it’s not easy. Do not TRY to learn It in the usual way. You need to spend time to understand hard parts. 

E.g.

Syntax elements:

1. Name of Measure is “Total Sales”.
2. operator (=) equals sign implies its beginning of the formula and returns the result.
3. Adds up all of the numbers in the COLUMN Sales[SalesAmount] using SUM Function
4. All functions require one or more argument surrounded by Parenthesis ()
5. Name of Table “Sales” 
6. The column [SalesAmount] in the Sales table and SUM function is used to AGGREGATE Sales.

• Here any Power BI report designers or consumers or developers or admins can suggest ideas(that could be anything new feature, or enhancement ETC.) which can improve user EXPERIENCE and currently that is what Microsoft is listening to User Voice”.
• Once this is posted in ideas PAGE, then different USERS across the world, possible needs similar or same KIND requirements and they start supporting idea by Voting it.
• No of votes increases then your idea has got demand which is called “Hot Idea”.
• Then Microsoft product team will consider it and releases that feature in subsequent releases.

• Data Stories GALLERY, is a place where all the DESIGNERS of Power BI across the DIFFERENT countries, those who have analyzed the data out of their personal interest and brought the data back to life and wanted to showcase the rest of the world about their outstanding skills.
• They upload Power BI (.PBIX) with their Storytelling and anyone can download their work.

Steps to go to Data Stories Gallery:

• Open PowerBI.com in favorite browser.
• By hovering on Learn
• Click on Community
• Scroll down a bit and you will FIND Data Stories Gallery.
• Also find Power BI reports where they have designed games.
• And anyone can submit her or his Data Story as well.

Yes, it’s possible by following the steps below:

• Firstly, Power BI Admin portal, in tenant settings “Analyze in EXCEL” option MUST be ENABLED by Power BI Administrator.
• Then at upper right corner, click settings to download and install Excel updates.
• Next left-hand side navigation pane, got to workspace > dataset > click on Eclipses (three dots) and select Analyze in Excel.
• Download .ODC, SAVE and double click on it.
• Post that NEED to provide power BI user id and password.
• Which will connect to dataset in Power BI.

• Power BI reports are created in Power BI Desktop.
• Advanced Q&A and tooltip option can be enabled.
• Various report page SIZE can be set like (16:8, 4:3, LETTER, Tool Tip, Cortana, and custom).
• Set Page Background and Wallpaper.
• Can use DrillThrough page, Bookmarks and Selection pane.
• Download Custom Visualizations from marketplace
• Set Color Themes to reports
• Can PRINTOUT of reports similar to Microsoft PowerPoint Presentations.
• Exported to PDF
• Contains 190+ DAX functions to apply on our data, whenever necessary.
• Save as TEMPLATE file (.PBIT).
• Report layout can be set to Desktop as well as Mobile.

• POWER BI Dashboards are available in Power BI web Service.
• Can use DRILLTHROUGH page, Bookmarks and Selection pane.
• Tiles can be directly pinned from reports to dashboards which gives very high-level DAY to day view of main KPIs of business and by a click on tiles will navigates back to reports for detailed view
• Report LAYOUT can be set to Mobile view.
• Apart from that various tiles can be created like Web Content, Images, Textbox and integrate URLs, Videos, and custom streaming DATA tile.

This major feature is introduced in JULY 2018 called Composite MODEL. Here, POWER BI desktop author is empowered to combine the data which is coming from a variety of sources and establish relationships between those tables in data model.

• DirectQuery, which is a large table in SQL server and need not to download in data model.
• Dual, which is a SMALL table in SQL server, can be downloaded easily into data model.
  However, this will be only in RAM will not be saved in .PBIX.
  To enable this feature, right clicking on the table in the Fields pane and select table properties, another window Field properties pane will appear and can change Storage MODE from DirectQuery to Dual
• Import, will import the data from sources like CSV or Excel or Azure SQL server or Azure Data Lake or Azure Cosmos to data model and will occupy the space in .PBIX

To attain this need to create Content Pack,

• Need to SELECT Specific Group of people (User Email ids or Distribution lists) (OR)
• My ENTIRE ORGANIZATION in then we choose give access to DASHBOARDS or Reports or Datasets.

This can be achieved by DYNAMIC Row Level Security(RLS) in Power BI.

Steps to be followed:

• In Modeling menu.
• Select Manage Roles.
• Create & define Role name.
• Select REQUIRED table and UserPricnicpalName() function and publish this to Power BI Service.

Once users LOG in to Power BI service with respective user id (UserPricnicpalName()) they will be able to SEE only data based on role we created.

Incremental refresh feature is released  in May 2018, it is traditionally RESERVED for large models (Like more than 1 GB) in analysis servers for high-end scalability of data by PUBLISHING only to workspace in Power BI Service where Premium CAPACITY is enabled for them.

Eg: It will increase the speed and efficiency of data refresh by just refreshing the data CHANGES in last 1 day -10 DAYS instead of loading last 3 years of data.

To enable it just right click on tables in fields pane and enable Incremental refresh. 

BASED on your organization and users Capacity, pricing must be planned.

• Pro Users are REQUIRED to collect, model, authors the reports and dashboards and publish to web services for sharing and COLLABORATION will be charged.
• Frequent Users are just CONSUMERS of reports and dashboards, Time frame of a week probably several. These users can have free license to access Reports and Dashboards as App Workspace is backed with PREMIUM capacity.
• Occasional Users are end-users where they very rarely use reports and dashboards. These users can have free license to access Reports and Dashboards  as App Workspace is backed with Premium capacity.

Premium Capacity is a way to have dedicated server/node for better handling Power BI Service Workload for your organization which supports up to 1 TB of data storage.

Premium capacity can be identified by seeing Diamond icon beside our APP workspace and it can be assigned by Office 365 GLOBAL ADMINISTRATOR (OR) Power BI Service Administrator.

Diamond icon

Steps:

• Open app.powerbi.com in FAVORITE browser.
• In NAVIGATION page, select respective app workspace
• Edit Settings
• Expand Advanced then enable Premium capacity for it.

Portals and centers are User friendly GUI tools available for organization which running in the cloud(Azure) and will help in providing access, managing users and security.

• Power BI Admin Portal: This portal helps to manage User metrics, Users, Audit logs and  tenant SETTINGS and Premium capacity settings in organization.
• Office 365 Admin Center: This center enables to manage users, configure, setup, groups and licensing, monitoring activities and see descriptive reports.
• Office 365 Security & Compliance Center: This center will help to protect data by providing right roles & permissions to users and also COMPLY with legal by following regulatory standards.
• Azure Active Directory (AAD) in the Azure portal: AAD is basically to manage IDENTITY and  Access Management which helps organization to provide maintain Single SIGN On(SSO) id for their users. In this portal, admin can add, remove, restrict the access for users (or) user groups (or) security groups in the organization.
• Powershell Cmdlets Administrative APIs & SDK: This part of azure portal, where admin can manage the users, user groups, security groups, etc.. by command-lets instead of GUI.

These roles are basically DEALS for USER access, governance and security and below are roles:

• Office 365 Global Administrator: This role is Super Admin for Office 365 Suite called Global administrator and will be able to grant various admin roles to others
• Office 365 Billing Administrator: This role used to Manage licenses or subscriptions.
• Power BI Service Administrator: This role is inside of O365 suite and used to manage User metrics, users, Audit Logs, tenant settings and Premium settings. Only O365 Global Admin can assign this role to others to help out better control on Power BI space.
• Power BI Premium CAPACITY Administrator: This role can be assigned to others by Power BI Service Admin and used to assign Premium capacity to app workspaces.
• Power BI Embedded Capacity Administrator: This role is available in MICROSOFT Azure portal. O365 Global and Power BI Service administrators are called as Power BI Embedded Capacity Admin.

Microsoft has categories Power BI users as below:

• Consumers (also called as End-Users) of Power BI who gets insights of the data in the form of reports and used for decision making.
• Designers who Collect, Wangle, Explore, Transform, Model, and Evaluate Data and then design reports, publish reports to Power BI Web Service and create dashboards.
• Administrators who are responsible for User governance using Office 365 (like Provisioning of Power BI Access to workspaces Licenses) and Administering User Security (like assigning Power BI admin ROLES and assigning Premium capacity roles)
• Developers who HELPS Power BI consumers and designers to BUILD integrating / embedding custom visuals in Power BI Reports and BUILDING streaming datasets.