Power BI Interview Questions and Answers: 60 Questions from Easy to Hard

Category  Total
Laptop      700
Phone       300
Tablet      150

Two Cats = CALCULATE ( [Total],
    Sales[Category] IN { "Laptop", "Phone" } )

Two Cats Keep = CALCULATE ( [Total],
    KEEPFILTERS ( Sales[Category] IN { "Laptop", "Phone" } ) )

-- Column filter: fast, replaces the Category filter
Laptop A = CALCULATE ( [Total], Sales[Category] = "Laptop" )

-- Table filter: scans Sales, keeps existing filters
Laptop B = CALCULATE ( [Total],
    FILTER ( Sales, Sales[Category] = "Laptop" ) )

Orders
Customer  Product  Amount
Asha      Pen          50
Asha      Pen          50
Ravi      Book        200

Total Amount = SUM ( Orders[Amount] )
Total Again  = SUMX ( Orders, [Total Amount] )

-- Counts the blank row added for unmatched keys
With Blank Row = COUNTROWS ( VALUES ( Product[Category] ) )

-- Ignores that blank row
Without = COUNTROWS ( DISTINCT ( Product[Category] ) )

Target Amount =
IF (
    ISFILTERED ( Product[Product Name] )
        || ISINSCOPE ( 'Date'[Date] ),
    BLANK (),
    SUM ( Targets[Amount] )
)

Closing Balance =
VAR LastDay =
    LASTNONBLANK (
        'Date'[Date],
        CALCULATE ( SUM ( Balances[Amount] ) )
    )
RETURN
    CALCULATE ( SUM ( Balances[Amount] ), LastDay )

-- Calculation group: Time Calc
Current = SELECTEDMEASURE ()

PY = CALCULATE ( SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

FYTD = CALCULATE ( SELECTEDMEASURE (),
    DATESYTD ( 'Date'[Date], "3/31" ) )

Metric = {
    ("Sales", NAMEOF ( 'Measures'[Total Sales] ), 0),
    ("Profit", NAMEOF ( 'Measures'[Total Profit] ), 1),
    ("Orders", NAMEOF ( 'Measures'[Order Count] ), 2)
}

-- Calculated column on Employee
Path = PATH ( Employee[EmpID], Employee[ManagerID] )

-- Role filter on Employee
VAR Me =
    LOOKUPVALUE (
        Employee[EmpID],
        Employee[Email], USERPRINCIPALNAME ()
    )
RETURN
    PATHCONTAINS ( Employee[Path], Me )

let
    Source = Sql.Database("sql01.corp.local", "SalesDW"),
    Sales = Source{[Schema = "dbo", Item = "FactSales"]}[Data],
    Filtered = Table.SelectRows(
        Sales,
        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
    )
in
    Filtered

Web.Contents(
    "https://api.example.in",
    [
        RelativePath = "v1/orders",
        Query = [from = "2025-04-01"]
    ]
)

let
    Source = Sql.Database("sql01", "SalesDW"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    Recent = Table.SelectRows(
        Orders, each [OrderDate] >= #date(2025, 4, 1)
    ),
    Slim = Table.SelectColumns(
        Recent, {"OrderID", "OrderDate", "Amount"}
    ),
    Indexed = Table.AddIndexColumn(Slim, "Row", 1, 1)
in
    Indexed

AddDate = Table.AddColumn(Source, "OrderDate",
    each DateTime.Date([OrderDateTime]), type date),
AddTime = Table.AddColumn(AddDate, "OrderTime",
    each DateTime.Time([OrderDateTime]), type time),
Slim = Table.RemoveColumns(AddTime, {"OrderDateTime"})

-- Before
Big Order Margin % =
IF (
    SUMX ( FILTER ( Sales, Sales[Amount] > 10000 ),
        Sales[Amount] ) = 0,
    BLANK (),
    SUMX ( FILTER ( Sales, Sales[Amount] > 10000 ),
        Sales[Profit] )
        / SUMX ( FILTER ( Sales, Sales[Amount] > 10000 ),
            Sales[Amount] )
)

-- After
Big Order Margin % =
VAR BigSales = CALCULATE ( SUM ( Sales[Amount] ),
    KEEPFILTERS ( Sales[Amount] > 10000 ) )
VAR BigProfit = CALCULATE ( SUM ( Sales[Profit] ),
    KEEPFILTERS ( Sales[Amount] > 10000 ) )
RETURN
    DIVIDE ( BigProfit, BigSales )