#!/usr/bin/env python3 """Which DAX measures in Microsoft's public sample reports sit on Snowflake's list of things its Power BI ingestion does not convert? pip install duckdb pbixray python3 dax_converter_gaps.py # run 1: the measurement, writes the CSV python3 dax_converter_gaps.py --plant # run 2: the same script, faults planted in its input Data: 12 sample reports from github.com/microsoft/powerbi-desktop-samples (MIT license), pinned to commit 64ff6b1476050e07a9011adb0894d25c385dc1f1. raw.githubusercontent.com sends no Last-Modified header, so each file is pinned by byte size and SHA-256 and the script stops if either differs. Files are read with pbixray (MIT); nothing is sent anywhere. List: https://docs.snowflake.com/en/user-guide/views-semantic/power-bi-ingestion (read 2026-10-18). The Databricks import page we read (Microsoft Learn, 2026-10-15) lists no functions, so there is no second column to measure against. """ import argparse import csv import hashlib import pathlib import re import sys import urllib.parse import urllib.request import duckdb import pandas as pd from pbixray import PBIXRay COMMIT = "64ff6b1476050e07a9011adb0894d25c385dc1f1" RAW = f"https://raw.githubusercontent.com/microsoft/powerbi-desktop-samples/{COMMIT}/" UA = ("dax_converter_gaps.py (https://www.datameaning.com/guides/" "which-dax-measures-a-converter-cannot-translate/; info@datameaning.com)") HERE = pathlib.Path(__file__).resolve().parent # (path in the repo, byte size, SHA-256). Pinned: the run stops if a file differs. FILES = [ ("2026 Power BI Samples Revamp/AdventureWorks Sales.pbix", 8390782, "76c89b64cd1c5a9514b43cc5ea356efc82f8e92f9a96b49f6679d9ccd57fc4f5"), ("2026 Power BI Samples Revamp/Artificial Intelligence Sample.pbix", 1120296, "170a12914593b42b3b2e7b1d2e05d6f923d30958edf23e46b93874206bfc7f8d"), ("2026 Power BI Samples Revamp/Competitive Marketing Analysis.pbix", 6612086, "00157525c8c82138fbd8202f967c58b8e800da1fa4ae2c47eaed3f538c653393"), ("2026 Power BI Samples Revamp/Corporate Spend.pbix", 716704, "2e56785790d20bc4596d2aec8ede7128093b4affaaf2204198b382c9192f6ca6"), ("2026 Power BI Samples Revamp/Employee Hiring and History.pbix", 8483276, "0763c8390a712ff37288eb1ad594ecb926f317c0bc9e0cce1132f8dc7037a9d9"), ("2026 Power BI Samples Revamp/Regional Sales Sample.pbix", 1018743, "050e6b8549c222489ef51ec6c7647609b9401d42da0daf793f91603dd2d8f3da"), ("2026 Power BI Samples Revamp/Revenue Opportunities.pbix", 245930, "3c95c4fb5a261e9d13a7152a2e14b18ea9e9efac0105dbd9ed46ba0137489c27"), ("2026 Power BI Samples Revamp/Store Sales.pbix", 9701642, "160b5b48b74ee1b5ea7259b6c8f0f1e2ec011e94c7b743742a14e6cad1153e6f"), ("Sample Reports/Human Resources Sample PBIX.pbix", 8700848, "f2de1fa89ef6170ac8942cd3caaed90fb412054f8037f75d6a4e8cf9eac7685d"), ("Sample Reports/Sales & Returns Sample v201912.pbix", 6530381, "c0a51fbae6c9e3c3cf0ff0564cc3408dad614449b5b27ec3e47fbaea4ba7f04b"), ("Sample Reports/Supply Chain Sample.pbix", 1177398, "dd55cac1348f80d546bfd71686f497917ae6eae4e4f7afd343061daf526a4d57"), ("Sample Reports/COVID-19 US Tracking Sample.pbix", 6917621, "3f0410a4fc092eea640b56ef4ba800b383431d6733f80042ef568de28a7fd66b"), ] # What Snowflake's page says, quoted and lightly condensed (read 2026-10-18). Every "named" key below # must appear in this text, so a typo in a key is caught by a check instead of silently matching nothing. VENDOR_TEXT = """ KEEPFILTERS and the time intelligence functions (PREVIOUSMONTH, SAMEPERIODLASTYEAR, TOTALYTD) are not. Functions like PREVIOUSMONTH, SAMEPERIODLASTYEAR, and TOTALYTD require report-level date context. SELECTEDVALUE, ALLSELECTED, and ISFILTERED describe what the reader clicked. MEDIAN, PERCENTILE, and the STDEV and VAR families. SUMMARIZE, TOPN, RANKX, CONTAINS, ISEMPTY, and COUNTROWS over VALUES. RELATEDTABLE is not. Calculation groups and calculated tables have no Snowflake table behind them, so they are dropped along with any measure that reads them. """ # The mapping table. basis "named": the function is written on Snowflake's page. # basis "family": a function we saw in the 12 reports that we read as belonging to a family the # page describes ("functions like ..."), so it is our judgment, not the vendor's. Disagree by # deleting a row: the script re-runs and the numbers move. # (key, regex for one function name, category, basis) MAPPING = [ ("PREVIOUSMONTH", r"PREVIOUSMONTH", "Time intelligence", "named"), ("SAMEPERIODLASTYEAR", r"SAMEPERIODLASTYEAR", "Time intelligence", "named"), ("TOTALYTD", r"TOTALYTD", "Time intelligence", "named"), ("KEEPFILTERS", r"KEEPFILTERS", "Filter context", "named"), ("SELECTEDVALUE", r"SELECTEDVALUE", "Selection", "named"), ("ALLSELECTED", r"ALLSELECTED", "Selection", "named"), ("ISFILTERED", r"ISFILTERED", "Selection", "named"), ("MEDIAN", r"MEDIANX?", "Statistical", "named"), ("PERCENTILE", r"PERCENTILEX?\.(?:INC|EXC)", "Statistical", "named"), ("STDEV", r"STDEVX?\.[SP]", "Statistical", "named"), ("VAR", r"VARX?\.[SP]", "Statistical", "named"), ("SUMMARIZE", r"SUMMARIZE", "Table function", "named"), ("TOPN", r"TOPN", "Table function", "named"), ("RANKX", r"RANKX", "Table function", "named"), ("CONTAINS", r"CONTAINS", "Table function", "named"), ("ISEMPTY", r"ISEMPTY", "Table function", "named"), ("COUNTROWS", None, "Table function", "named"), # COUNTROWS(VALUES(...)) only: matched on text ("RELATEDTABLE", r"RELATEDTABLE", "Related table", "named"), ("DATEADD", r"DATEADD", "Time intelligence", "family"), ("DATESINPERIOD", r"DATESINPERIOD", "Time intelligence", "family"), ("ISINSCOPE", r"ISINSCOPE", "Selection", "family"), ("ISCROSSFILTERED", r"ISCROSSFILTERED", "Selection", "family"), ] COUNTROWS_VALUES = re.compile(r"\bCOUNTROWS\s*\(\s*VALUES\s*\(", re.I) TOKEN = re.compile(r'"(?:[^"]|"")*"|/\*.*?\*/|//[^\n]*|--[^\n]*|\'[^\']*\'', re.S) REF = re.compile(r"(?:'([^']+)'|\b([A-Za-z_][A-Za-z0-9_]*))?\[([^\]]+)\](?:\.\[[^\]]+\])*") # 'Calendar'[Date].[Month] is a date-hierarchy level of a column AUTO_DATE = re.compile(r"^(LocalDateTable|DateTableTemplate)_[0-9a-f-]{36}$", re.I) # made by Power BI itself CALL = re.compile(r"\b([A-Za-z_][A-Za-z0-9_.]*)\s*\(") def strip(expr): """Remove string literals and comments; keep 'Quoted Table' names (they are references).""" def f(m): s = m.group(0) return s if s.startswith("'") else ('""' if s.startswith('"') else " ") return TOKEN.sub(f, expr) def functions(clean): """Function names called in an expression ([column] and 'table' text removed first).""" t = re.sub(r"\[[^\]]*\]", "[]", re.sub(r"'[^']*'", "T", clean)) return {m.group(1).upper() for m in CALL.finditer(t)} def causes_of(expr, own_table, ctx): """Causes found in one expression: (kind, key). kind: fn_named, fn_family, calc_group, calc_table. Also returns the measure names it references and the references it could not resolve.""" clean = strip(expr) causes, refs, unresolved = set(), set(), [] fns = functions(clean) for key, rx, _cat, basis in MAPPING: kind = "fn_named" if basis == "named" else "fn_family" if key == "COUNTROWS": if COUNTROWS_VALUES.search(clean): causes.add((kind, key)) elif any(re.fullmatch(rx, f) for f in fns): causes.add((kind, key)) for m in REF.finditer(clean): tbl, bare, name = m.group(1) or m.group(2), None, m.group(3) tl, nl = (tbl or "").lower(), name.lower() if tbl and (tl, nl) in ctx["columns"]: pass elif nl in ctx["measures"]: refs.add(nl) elif not tbl and (own_table.lower(), nl) in ctx["columns"]: pass else: unresolved.append(f"{tbl or ''}[{name}]") # tables read by name, with or without a column: 'Time Intelligence'[...] or ALL('Calc Table') names = {m.group(0).strip("'").lower() for m in re.finditer(r"'[^']*'", clean)} names |= {w.lower() for w in re.findall(r"\b[A-Za-z_][A-Za-z0-9_]*\b", re.sub(r"\[[^\]]*\]", "", clean))} for t in names: if t in ctx["calc_groups"]: causes.add(("calc_group", t)) elif t in ctx["calc_tables"]: causes.add(("calc_table", t)) return causes, refs, unresolved def fetch(path, size, sha, dest): f = dest / (hashlib.sha1(path.encode()).hexdigest()[:10] + ".pbix") url = RAW + urllib.parse.quote(path) head = urllib.request.urlopen(urllib.request.Request(url, headers={"User-Agent": UA}, method="HEAD"), timeout=120) lm = head.headers.get("Last-Modified") or "not sent" if not f.exists() or f.stat().st_size != size: r = urllib.request.urlopen(urllib.request.Request(url, headers={"User-Agent": UA}), timeout=120) f.write_bytes(r.read()) got = hashlib.sha256(f.read_bytes()).hexdigest() if f.stat().st_size != size or got != sha: sys.exit(f"STOP: {path} differs from the pin (bytes {f.stat().st_size}, sha256 {got})") return f, lm def main(): ap = argparse.ArgumentParser() ap.add_argument("--plant", action="store_true", help="run 2: plant faults in the input") ap.add_argument("--data-dir", default="loop-out/data/dax-samples") a = ap.parse_args() dest = pathlib.Path(a.data_dir) dest.mkdir(parents=True, exist_ok=True) print(f"Source: microsoft/powerbi-desktop-samples, commit {COMMIT}, MIT license") print("Mode:", "RUN 2, faults planted in the input" if a.plant else "RUN 1, the measurement") from importlib.metadata import version print(f"Versions: python {sys.version.split()[0]}, duckdb {duckdb.__version__}, " f"pbixray {version('pbixray')}, pandas {pd.__version__}") print(f"{'file':<62}{'bytes':>10} last-modified sha256 (first 12)") rows, other, source_counts, ctxs = [], [], {}, {} total_bytes = 0 for path, size, sha in FILES: f, lm = fetch(path, size, sha, dest) total_bytes += size print(f"{path:<62}{size:>10} {lm:<14} {sha[:12]}") m = PBIXRay(str(f)) sample = pathlib.PurePosixPath(path).stem dm = m.dax_measures.copy() source_counts[sample] = len(dm) cg = m.tmschema_calculation_groups ci = m.tmschema_calculation_items ct = m.dax_tables calc_groups = {t.lower() for t in cg["TableName"]} if len(cg) else set() mine = [t for t in ct["TableName"] if not AUTO_DATE.match(t)] if len(ct) else [] calc_tables = {t.lower() for t in mine} cols = {(r.TableName.lower(), r.ColumnName.lower()) for r in m.schema.itertuples()} cols |= {(r.TableName.lower(), r.ColumnName.lower()) for r in m.dax_columns.itertuples()} ctxs[sample] = dict(columns=cols, calc_groups=calc_groups, calc_tables=calc_tables) other.append(dict(sample=sample, calculation_groups=len(cg), calculation_items=len(ci), calculated_tables=len(mine), auto_date_tables=len(ct) - len(mine), calculated_columns=len(m.dax_columns), measures=len(dm))) for r in dm.itertuples(): rows.append(dict(sample=sample, table=r.TableName, measure=r.Name, expression=r.Expression if isinstance(r.Expression, str) else None)) if a.plant and sample == "AdventureWorks Sales": rows += [ # a duplicated row dict(sample=sample, table="Sales", measure="Cost", expression="SUM('Sales'[Total Product Cost])"), # reaches SAMEPERIODLASTYEAR only through another measure dict(sample=sample, table="Sales", measure="Planted Sales PY", expression="CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))"), dict(sample=sample, table="Sales", measure="Planted PY Margin", expression="DIVIDE([Planted Sales PY], [Sales])"), # a reference to a measure that does not exist dict(sample=sample, table="Sales", measure="Planted Broken Ref", expression="[No Such Measure] * 2"), # a missing value dict(sample=sample, table="Sales", measure="Planted Empty", expression=None), ] print(f"Total downloaded: {total_bytes:,} bytes in {len(FILES)} files\n") mapping = list(MAPPING) if a.plant: # typos in the mapping table mapping += [("SAMEPERIODLASTYAER", r"SAMEPERIODLASTYAER", "Time intelligence", "named"), ("ISINSCOP", r"ISINSCOP", "Selection", "family")] # ---- resolve every measure info = {} seen_functions = set() for r in rows: ctx = dict(ctxs[r["sample"]]) ctx["measures"] = {x["measure"].lower() for x in rows if x["sample"] == r["sample"]} expr = r["expression"] if not expr or not expr.strip(): info[(r["sample"], r["measure"].lower(), id(r))] = (r, None) continue seen_functions |= functions(strip(expr)) causes, refs, unresolved = causes_of(expr, r["table"], ctx) info[(r["sample"], r["measure"].lower(), id(r))] = (r, (causes, refs, unresolved)) by_name = {} for (s, n, _), (r, res) in info.items(): by_name.setdefault((s, n), []).append((r, res)) def reach(sample, name, allowed, seen=()): """Causes reachable from a measure through the measures it references, with the path.""" out = {} for _r, res in by_name.get((sample, name), []): if res is None: continue causes, refs, _ = res for c in sorted(causes): if c[0] in allowed: out.setdefault(c, [name]) for ref in sorted(refs): # sorted, so the path shown is the same on every run if ref in seen or ref == name: continue for c, p in reach(sample, ref, allowed, seen + (name,)).items(): out.setdefault(c, [name] + p) return out LISTED = {"fn_named", "calc_group", "calc_table"} BROAD = LISTED | {"fn_family"} out_rows, unresolved_all, unscorable = [], [], 0 for (s, n, _), (r, res) in info.items(): base = dict(sample=s, set="Samples Revamp" if "Revamp" in next(p for p, _, _ in FILES if pathlib.PurePosixPath(p).stem == s) else "Sample Reports", table=r["table"], measure=r["measure"], expression_chars=len(r["expression"]) if r["expression"] else 0) if res is None: unscorable += 1 out_rows.append({**base, "status_listed": "Unscorable", "status_broad": "Unscorable", "causes": "", "own_causes": "", "via": "", "direct_scan_finds": "no"}) continue causes, refs, unresolved = res unresolved_all += [(s, r["measure"], u) for u in unresolved] entry = dict(base) for label, allowed in (("listed", LISTED), ("broad", BROAD)): own = {c for c in causes if c[0] in allowed} if any(k.startswith("fn_") for k, _ in own): st = "Direct function" elif any(k == "calc_group" for k, _ in own): st = "Calculation group" elif any(k == "calc_table" for k, _ in own): st = "Calculated table" elif reach(s, n, allowed): st = "Indirect" else: st = "Clear" entry["status_" + label] = st rc = reach(s, n, BROAD) entry["own_causes"] = ";".join(sorted(f"{k}:{v}" for k, v in causes)) entry["causes"] = ";".join(sorted(f"{k}:{v}" for (k, v), _ in rc.items())) # the chain behind an Indirect status, from the listed view when that one is Indirect view = LISTED if entry["status_listed"] == "Indirect" else BROAD if entry["status_broad"] == "Indirect" else None paths = list(reach(s, n, view).values()) if view else [] entry["via"] = " > ".join(min(paths, key=lambda p: (len(p), p))[1:]) if paths else "" entry["direct_scan_finds"] = "yes" if any(k == "fn_named" for k, _ in causes) else "no" out_rows.append(entry) df = pd.DataFrame(out_rows).sort_values(["sample", "table", "measure"], kind="stable").reset_index(drop=True) con = duckdb.connect() con.register("m", df) con.execute("CREATE TABLE measures AS SELECT * FROM m") # ---- checks: each says which planted fault trips it corpus_fns = seen_functions checks = [] n_loaded = con.execute("SELECT count(*) FROM measures").fetchone()[0] n_source = sum(source_counts.values()) checks.append(("rows loaded = rows pbixray reads from the files", n_loaded == n_source, f"loaded {n_loaded}, source {n_source}", "a duplicated or invented row")) dups = con.execute("SELECT sample, lower(measure), count(*) FROM measures GROUP BY ALL HAVING count(*) > 1").fetchall() checks.append(("no measure appears twice in one report", not dups, f"{len(dups)} duplicated name(s)", "a duplicated row")) checks.append(("every measure has an expression", unscorable == 0, f"{unscorable} without one", "a missing value")) checks.append(("every reference resolves to a measure or column", not unresolved_all, f"{len(unresolved_all)} unresolved " + "; ".join(f"{s}: {m} -> {u}" for s, m, u in unresolved_all[:6]), "a reference to a measure that does not exist")) bad_named = [k for k, _, _, b in mapping if b == "named" and k.lower() not in VENDOR_TEXT.lower()] checks.append(("every vendor-named key is in the vendor's text", not bad_named, f"not found: {', '.join(bad_named) or 'none'}", "a typo in a vendor-named key")) bad_family = [k for k, _, _, b in mapping if b == "family" and k not in corpus_fns] checks.append(("every family key is a function seen in the reports", not bad_family, f"not seen: {', '.join(bad_family) or 'none'}", "a typo in a family key")) print("Checks") for name, ok, detail, trips in checks: print(f" [{'pass' if ok else 'FAIL'}] {name}: {detail} (tripped by: {trips})") failed = [c for c in checks if not c[1]] if a.plant: print("\nPlanted measures, as the resolver sees them") for r in con.execute("""SELECT measure, status_listed, via, direct_scan_finds FROM measures WHERE measure LIKE 'Planted%' ORDER BY measure""").fetchall(): print(f" {r[0]:<20} status={r[1]:<16} via={r[2] or '-':<18} a direct-call scan finds it: {r[3]}") if failed: print(f"\nSTOPPED: {len(failed)} check(s) failed. No result table and no CSV from this run.") sys.exit(1) # ---- results print("\nKeys of the mapping table and where they matched (all 12 reports)") print(f" {'key':<20}{'category':<18}{'basis':<8}{'measures hit directly':>22}") for key, rx, cat, basis in mapping: kind = "fn_named" if basis == "named" else "fn_family" hits = con.execute("SELECT count(*) FROM measures WHERE list_contains(string_split(own_causes, ';'), ?)", [f"{kind}:{key}"]).fetchone()[0] print(f" {key:<20}{cat:<18}{basis:<8}{hits:>22}") print(" (a key with 0 hits is named by the vendor but not used by any of these 12 reports)") tot = con.execute("SELECT count(*) FROM measures").fetchone()[0] print("\nBy report: measures that need a person (status other than Clear)") print(f" {'report':<34}{'measures':>9}{'listed':>8}{'share':>8}{'broad':>7}{'share':>8}{'missed by direct scan':>23}") q = """SELECT sample, count(*) n, sum((status_listed <> 'Clear')::int) listed, sum((status_broad <> 'Clear')::int) broad, sum((status_listed <> 'Clear' AND direct_scan_finds = 'no')::int) missed FROM measures GROUP BY sample ORDER BY sample""" for s, n, l, b, mi in con.execute(q).fetchall(): print(f" {s:<34}{n:>9}{l:>8}{100*l/n:>7.1f}%{b:>7}{100*b/n:>7.1f}%{mi:>23}") n, l, b, mi = con.execute("""SELECT count(*), sum((status_listed <> 'Clear')::int), sum((status_broad <> 'Clear')::int), sum((status_listed <> 'Clear' AND direct_scan_finds = 'no')::int) FROM measures""").fetchone() print(f" {'ALL 12 REPORTS':<34}{n:>9}{l:>8}{100*l/n:>7.1f}%{b:>7}{100*b/n:>7.1f}%{mi:>23}") print("\nBy status (listed = Snowflake's named functions, calculation groups and calculated tables; broad adds the family rows)") print(f" {'status':<20}{'listed':>8}{'share':>8}{'broad':>7}{'share':>8}") order = ["Direct function", "Calculation group", "Calculated table", "Indirect", "Clear"] cl = dict(con.execute("SELECT status_listed, count(*) FROM measures GROUP BY 1").fetchall()) cb = dict(con.execute("SELECT status_broad, count(*) FROM measures GROUP BY 1").fetchall()) for st in order: print(f" {st:<20}{cl.get(st, 0):>8}{100*cl.get(st, 0)/tot:>7.1f}%{cb.get(st, 0):>7}{100*cb.get(st, 0)/tot:>7.1f}%") print(f" {'Total':<20}{sum(cl.values()):>8}{100*sum(cl.values())/tot:>7.1f}%{sum(cb.values()):>7}{100*sum(cb.values())/tot:>7.1f}%") print("\nDirect function hits by category (a measure counts once per category)") cat_of = {(("fn_named" if b == "named" else "fn_family") + ":" + k): c for k, _, c, b in mapping} cats = {} for (causes,) in con.execute("SELECT own_causes FROM measures WHERE status_broad = 'Direct function'").fetchall(): for c in {cat_of[x] for x in causes.split(";") if x in cat_of}: cats[c] = cats.get(c, 0) + 1 for c, k in sorted(cats.items(), key=lambda x: (-x[1], x[0])): print(f" {c:<20}{k:>5}") print("\nObjects that are not measures (Snowflake drops calculation groups and calculated tables)") print(f" {'report':<34}{'calc groups':>12}{'calc items':>11}{'calc tables':>12}{'auto date':>10}{'calc columns':>13}") for o in sorted(other, key=lambda x: x["sample"]): print(f" {o['sample']:<34}{o['calculation_groups']:>12}{o['calculation_items']:>11}" f"{o['calculated_tables']:>12}{o['auto_date_tables']:>10}{o['calculated_columns']:>13}") print(f" {'ALL 12 REPORTS':<34}" + "".join(f"{sum(o[k] for o in other):>{w}}" for k, w in ( ("calculation_groups", 12), ("calculation_items", 11), ("calculated_tables", 12), ("auto_date_tables", 10), ("calculated_columns", 13)))) print(" (auto date = tables Power BI makes itself for date/time columns; not counted as calc tables)") # ---- the CSV, then read back: shares must sum to 100% out = HERE / "dax_measure_status.csv" cols = ["sample", "set", "table", "measure", "status_listed", "status_broad", "causes", "via", "direct_scan_finds", "expression_chars"] with open(out, "w", newline="", encoding="utf-8") as fh: w = csv.writer(fh) w.writerow(cols) for r in df[cols].itertuples(index=False): w.writerow(list(r)) back = list(csv.DictReader(open(out, encoding="utf-8"))) shares = {} for r in back: shares[r["status_listed"]] = shares.get(r["status_listed"], 0) + 1 ok = len(back) == n_source and abs(sum(100 * v / len(back) for v in shares.values()) - 100) < 1e-9 print(f"\nCSV written: {out.name}, {len(back)} rows (source {n_source}); status shares sum to " f"{sum(100 * v / len(back) for v in shares.values()):.1f}%: {'pass' if ok else 'FAIL'}") sys.exit(0 if ok else 1) if __name__ == "__main__": main()