31.7 Infosys, Wipro and Accenture

M60. What is Row-Level Security, what types are there, and how would you stop the HR department from seeing other departments' data?

Reported for: Infosys [S15] [S16] · also Wipro [S19], Tech Mahindra [S24] [S25], Accenture [S20] [S21], Deloitte [S1] [S3], Genpact [S29], EY [S7], LTIMindtree [S27] [S28], Mu Sigma [S30], Amazon [S32]

RLS filters the rows a user can see. Static RLS: one role per group with a fixed rule, for example role Pune with [City] = "Pune". Dynamic RLS: one role whose rule uses the signed-in user, for example [Manager Email] = USERPRINCIPALNAME() on DarkStore, or a User Security mapping table (Email, City) related to DarkStore (Module 26.3). For the HR example: add a mapping of users to the departments they may see, filter the department dimension with USERPRINCIPALNAME(), and test with View as in Desktop and Test as role in the Service. Remember that RLS applies to viewers; workspace Admins, Members and Contributors see all data.

M61. What is the difference between RANK and RANKX?

Reported for: Infosys [S16] · also Fractal [S31] (RANKX and ties)

RANKX (the classic function) iterates a table, evaluates an expression for each row and ranks the current value: RANKX(ALL(Product[Product Name]), [Total Sales], , DESC, Dense). Its ties argument is Skip by default (1, 2, 2, 4), and Dense gives 1, 2, 2, 3. RANK is a newer window function (added in 2023) that ranks rows in a relation using ORDERBY and PARTITIONBY, similar to SQL's RANK/DENSE_RANK, and handles ties with DENSE or SKIP. RANKX is still the one most people use in measures.

M62. How many types of relationships are there, and how do you use an inactive relationship?

Reported for: Infosys [S16] · also Accenture [S21], Genpact [S29]

By cardinality: one-to-many/many-to-one, one-to-one and many-to-many. By direction: single or both. By state: active (solid line, used automatically) and inactive (dotted line). Only one active path can exist between two tables. Orders has Order Date (active) and Delivered Date (inactive) to the Date table. Activate the inactive one inside a measure with USERELATIONSHIP (M35).

M63. What will you do if a DAX formula shows an error?

Reported for: Infosys [S16]

Read the message: it usually names the problem (a missing column, a table of multiple values, a circular dependency). Then break the formula into variables and return each variable one at a time to find the failing part, check data types (text vs number), check whether you are in a row context or filter context, and test the formula in a simple table visual or in DAX query view. Use DIVIDE instead of "/" and handle blanks.

M64. When would you use a snowflake schema instead of a star?

Reported for: Infosys [S16] · also Capgemini [S23], EY [S5], Accenture [S20] [S21], LTIMindtree [S27] [S28] (star schema), Tech Mahindra [S25]

In a star schema, each dimension is one flat table around the fact (Orders → Product, DarkStore, Customer, Date). In a snowflake, dimensions are normalised into chains (Product → Sub Category → Category). Power BI prefers the star because it has fewer joins, simpler DAX and better performance. A snowflake is acceptable when a dimension is very large and shared attributes would repeat heavily, when the source warehouse already delivers it that way and flattening is impractical, or when a lookup is shared by several dimensions. Even then, it is common to flatten in Power Query.

M65. What is the "Z-order" of visuals?

Reported for: Infosys [S15]

In a Power BI report, Z-order is the layering order of objects on the canvas: which visual, shape or button sits in front of which. Change it with Format › Arrange › Bring forward / Send backward, or by dragging items in the Selection pane (the top item is in front). It matters for background shapes behind cards and for overlapping bookmark layouts. (In the data-lake world, "Z-ordering" is also a Delta table optimisation technique. Mention it only if the role involves Fabric or Databricks.)

M66. How do you create a calculated table?

Reported for: Wipro [S17]

Modeling › New table, then write a DAX table expression:

Pune Orders = FILTER(Orders, RELATED(DarkStore[City]) = "Pune")
Date = CALENDAR(DATE(2024,4,1), DATE(2027,3,31))
City Summary = SUMMARIZECOLUMNS(DarkStore[City], "Sales", [Total Sales])

Calculated tables are computed at refresh and stored in the model. For data preparation, Power Query is usually the better place.

M67. How do you connect an Excel file stored in OneDrive or SharePoint?

Reported for: Wipro [S18]

Open the file's details in OneDrive/SharePoint and copy its path (a URL ending with the file name, without the "?web=1" part), then use Get Data › Web with that URL and sign in with an Organizational account. Alternatively, use SharePoint folder and filter to the file. Cloud files like this refresh in the Service without a gateway.

M68. Power BI crashes when loading a large dataset. What do you do?

Reported for: Wipro [S17] · also Deloitte [S1] (large datasets), Tech Mahindra [S25]

Load less: filter rows early (a date-range parameter in development), remove unneeded columns before loading, aggregate at the source with a SQL view, split DateTime columns, and turn off Auto date/time. Use incremental refresh, or DirectQuery/composite models for the largest tables. Make sure you use 64-bit Power BI Desktop and enough RAM, and close other heavy apps.

M69. What are the components of Power BI, and the three icons on the left in Desktop?

Reported for: Accenture [S21] · also Deloitte [S1], Wipro [S19], PwC [S11], Mu Sigma [S30], EY [S7]

Components: Power BI Desktop (authoring), Power BI Service (cloud sharing, refresh, apps), Power BI Mobile, on-premises data gateway, Report Server (on-premises) and Report Builder (paginated reports). Inside Desktop, Power Query, the VertiPaq engine and DAX do the work. The classic three views on the left are Report, Table (Data) and Model. Newer versions also show DAX query view and TMDL view (Module 2.2).

M70. How do you add comments in DAX, and what are the "X" functions?

Reported for: Accenture [S21]

Comments: // or -- for a single line, and /* … */ for several lines. "X" functions are iterators (SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX, CONCATENATEX). They loop through a table row by row, evaluate an expression and then aggregate the results (M23).

Avg Basket per City =        // average order value per city
AVERAGEX(VALUES(DarkStore[City]), [AOV])

M71. What is the difference between drill down and drill through?

Reported for: Accenture [S20] · also Deloitte [S1] [S4], Genpact [S29], Mu Sigma [S30], PwC [S11], HCLTech [S26]

Drill down moves to a lower hierarchy level inside the same visual (Year → Quarter → Month, or City → Area → Store). Drill through jumps to a separate detail page filtered by what you right-clicked (right-click Nashik › Drill through › City Details). Build the target page with the drill-through field in its drill-through well and add a Back button (Modules 16–17).