Ravindra BagaleCourses & study guides मराठी Track your progress

29. Practice Exercises and Final Assignment

29.4 Module 8: Add Column, Calculated Columns and Measures

By Last updated:

  1. Use Column From Examples to create First Name from Customer Name.
  2. Create a Custom Column Order Line Total handling null Discount with ??.
  3. Write fnDeliveryBand and call it with Invoke Custom Function in both the Blinkit and Amazon Now queries.
  4. Add an Index column starting at 1001 with increment 1.
  5. Parse a JSON Drop Location column into lat, lng and area.
  6. Create 5-minute delivery buckets with Standard › Integer-Divide and Multiply.
  7. Add Order Date, Order Hour and Day Name using Date and Time menus.
  8. DAX calculated columns: Category (RELATED), Delivery Speed Band (SWITCH) with a sort column, Is Weekend in the Date table.
  9. Implement Net Sales both as a column + SUM and as a SUMX measure. Compare results and model size.
  10. Explain why "% Under 10 Mins" must be a measure.
  11. Create a _Measures table, display folders and ₹ formats. Add a dynamic format string for Total Sales.

Practice files (fictional): column_vs_measure_orders.csv, pq_customers_text.csv, visuals_orders_apr_may_2026.csv

Hints
  • Add Column keeps originals; DAX columns are evaluated at refresh, measures at query time.
Solutions (checked on the practice files)
  1. Ravindra, Shraddha, Ruhi, Salman.
  2. [Amount] - ([Discount] ?? 0) on column_vs_measure_orders.csv → 450, 300, 600, 400, 0 = ₹1,750.
  3. Bands: Within 10 min 2 (201, 203), 11-20 min 2 (202, 204), Not delivered 1 (205).
  4. Add Column › Index Column › Custom › Starting index 1001, increment 1 → 1001…1005.
  5. Parse › JSON, then expand lat, lng, area (fields depend on your sample).
  6. Integer-Divide 5 then Multiply 5: 5, 10, 5, 15, null.
  7. Visuals file: 06-Apr-2026 = Monday, 05-May-2026 = Tuesday.
  8. RELATED needs a many-to-one relationship; SWITCH(TRUE(), …) for bands; add a numeric sort column and use Sort by column.
  9. Both give ₹1,750 on the practice file; the column adds stored data (larger model), the SUMX measure does not.
  10. Ratios must be recalculated for each filter: on the file, % under 10 = 2 ÷ 4 delivered = 50% – averaging row percentages would be wrong.
  11. _Measures table, display folders, Currency ₹ English (India); dynamic format string returns e.g. "₹#,0,.0K" above 1,000.

Ravindra Bagale's Tip

Friends, many students check a measure only in the grand total. Test every measure in a table visual by City and by Month, and compare one or two cells with a manual calculation. Keep this in mind!