Migrate from Power BI

How to rebuild a Power BI semantic model as a Malloy data model with the modeling agent and its Power BI skill, how DAX maps to Malloy, which measures change meaning, and how to check the numbers, for Power BI developers and analytics engineers

A Power BI semantic model (tables, relationships, DAX measures and row-level security roles) moves into Credible through the modeling agent, which has a dedicated skill for Power BI, malloy-powerbi-review (opens in a new tab) (MIT-licensed, in Malloy Publisher). The agent loads it when it finds a Power BI model and you confirm it should be used. The skill routes every DAX measure to a translation recipe, says which ones can return a different number in Malloy, and checks the numbers against Power BI.

Comparing the two products first? See Credible vs. Power BI.

What Credible Reads

The skill accepts the model in these shapes, best first:

ShapeWhat it isNotes
TMDL folder (definition/*.tmdl)The model as textRead directly. Preferred
PBIP project (*.SemanticModel/definition/)A Power BI project, which contains the TMDL folderRead directly
model.bimThe older single-file JSON formatRead directly
.pbixA binary file containing the model and imported dataNeeds third-party extraction, which can decode a column to wrong values without an error. Use only if you cannot re-save

From the model it reads tables, columns, measures, calculated columns and tables, calculation groups, relationships, /// descriptions, hidden objects, perspectives and RLS roles. It reads the Power Query (M) code only to find the real source table behind each partition. It does not read report layout (report.json, pages, visuals).

The skill also includes a script, classify_measures.py, that routes every measure in one pass and builds the dependency graph between measures. It needs a shell, so it runs when you build with your coding agent. In the Credible App the agent follows the same recipes without the script.

Prerequisites

  • A Credible environment with a connection to the warehouse the Power BI model reads. An import-mode model with no warehouse behind it, or a DirectQuery or live-connection model with no data in the file, can still be translated, but nothing can be validated until a connection to the same data exists.
  • The model saved as a Power BI project (.pbip). In Power BI Desktop the option is a preview feature: turn it on under File > Options and settings > Options > Preview features, restart Desktop, then use File > Save as. It is not available in Power BI Desktop for Report Server.
  • A local modeling directory set up with cred init. See Cred CLI. The Power BI skill is part of the modeling skills it installs. It is also available in the Credible App, where you can paste individual DAX measures into the chat.
  • The current values of the measures people rely on, at known filters. A screenshot of the report is enough.

Steps

  1. Copy the model into the directory. Put the *.SemanticModel folder (or the definition/ folder) in a subfolder such as powerbi/, and add that folder to .credignore so cred publish leaves it out of the package.
  2. Ask the agent to model from it. For example: "Migrate the Power BI model in powerbi/ to Malloy." The agent inventories the model: storage mode per table, the real source tables, relationships, and measure counts, including how many use CALCULATE or time intelligence. Review that inventory before any translation starts.
  3. Decide where the data comes from. If you are moving off Power BI, confirm the warehouse behind the model is still there and that the connection can read it.
  4. Review tables, relationships and fields. Table, column and measure names, descriptions and visibility carry across. Auto date tables (LocalDateTable_*) are skipped, and one real date dimension is proposed if the model has none.
  5. Review the measure translations. The agent routes each measure: report-layer measures (titles, captions, tooltip text) are skipped, direct translations are written, and measures whose meaning can change are flagged with the filter that would expose the difference.
  6. Decide the RLS roles. See Row-Level Security.
  7. Check parity and coverage, then publish.

How Power BI Maps to Malloy

Model objects

Power BIMalloyNotes
tablesource:One source per table
columnA column reference, or a dimension: when renamed or derived
Calculated columndimension: with the translated expressionOr moved upstream
Calculated tableA query-based source, or moved upstream
measuremeasure:The translation needs care; see below
Partition source (from M)connection.table('schema.table')M transformation steps are not translated
/// description#(doc) tag
formatStringRender tags such as # currency, # percent
isHiddenClassified by reasonNot mapped to internal: automatically
PerspectiveAccess modifiers, or a separate source
Auto date tablesSkipped

Relationships

Power BIMalloyNotes
Many-to-one (the default)join_one: on the many side
One-to-onejoin_one:
Many-to-manyFlaggedModeled through a bridge at the real grain
crossFilteringBehavior: bothDirectionsFlaggedNo Malloy equivalent; the agent asks what it was for
isActive: falseA second, named joinMeasures that used USERELATIONSHIP refer to the named join instead

DAX

DAXMalloyNotes
SUM, AVERAGE, MIN, MAXsum(), avg(), min(), max()
SUMX(T, row expression)sum(expression)
COUNTROWS(T)count()
DISTINCTCOUNT(T[c])count(c)count(c) is already distinct in Malloy
COUNT(T[c])count() { where: c is not null }Not count(c)
DIVIDE(a, b)a / nullif(b, 0)
IF, SWITCH(TRUE(), ...)pick ... when ... else
RELATED(Other[c])other.cA join path
CALCULATE(expr, KEEPFILTERS(T[c] = "x"))expr { where: c = 'x' }Same meaning
CALCULATE(expr, T[c] = "x")expr { where: c = 'x' }, flaggedCan differ; see below
CALCULATE(expr, ALLSELECTED())all(expr)
ALL, REMOVEFILTERSall() or exclude(), flaggedCan differ; see below
ALLEXCEPT(T, T[keep])all(expr, keep)
RANKX, TOPNcalculate: rank() in a query
TOTALYTD, DATESYTDsum_cumulative() partitioned by year in a query
SAMEPERIODLASTYEAR, DATEADDFiltered aggregates over named ranges, or lag() when no period is missing
What-if parameter with SELECTEDVALUEA given: value set at query time
EARLIER, EARLIESTNo recipeThe agent asks what the number means

Measures that can change meaning

A DAX Boolean filter in CALCULATE replaces any existing filter on that column. Malloy's where: adds to the existing filters. The same measure, transcribed faithfully, can return a different number when a report filters the same column. ALL and REMOVEFILTERS diverge the same way: DAX removes filters, while Malloy's all() removes grouping and keeps the query's filters.

Both kinds match at an unfiltered grand total, which is the number people usually check first. The agent flags every measure routed to one of these recipes and names the filter to test it with.

Four patterns translate with a stated cost: date spines (filling periods that have no rows), semi-additive measures such as closing balances, calculation groups, and parent-child hierarchies. The agent writes working Malloy for each and explains the trade-off.

Row-Level Security

Each role's tablePermission filter is classified. Simple filters fit Malloy's access rules: [Email] = USERPRINCIPALNAME() becomes an #(access_filter) on the email column, compared with a secure given. Filters that use ||, NOT, <>, function calls or a security lookup table do not fit the rule grammar as written and need a decision.

Two differences to plan for:

  • Filters don't propagate through joins. In Power BI, a filter on a dimension table restricts every fact table related to it. In Malloy, a rule applies to the source a query starts from. Every source a query can start from needs its own rule.
  • One role per value is not a row filter. A set of roles such as one per region ([Region] = "East") usually collapses into one rule that compares the region column with the caller's region. Where populations differ in kind, each becomes its own source, built on a base source that nobody can query directly (#(authorize) false).

Prove Parity

  1. Pick the measures the business looks at every day.
  2. Get each value from Power BI at a stated filter, for example one year and one region.
  3. Run the Malloy equivalent at the same filter and compare.
  4. Repeat with a filter on the same column a flagged measure filters. That is where the differences show.
  5. If data was extracted from a .pbix, compare row counts and one column total per table before modeling on top of it.

The agent reports the result as a table of measure, filter, Power BI value, Malloy value and match, alongside a coverage report of tables, measures by route, relationships and roles.

Troubleshooting

ProblemCauseFix
.pbip doesn't appear in Save asThe Power BI project format is a preview featureTurn it on in Options, then restart Power BI Desktop
A measure matches at the total but not when slicedA CALCULATE filter or ALL that replaces a filter in DAX and adds one in MalloyAsk the agent for the recipe it used and decide which meaning the business wants
The model has no source tablesSome partitions are built in Power Query from an API call or a file on someone's machine, so there is nothing in the warehouse for a source to point atDecide where that data will live before modeling it

Example

table Sales
	/// Total revenue in USD
	measure 'Total Revenue' = SUM('Sales'[Amount])
		formatString: \$#,0.00

	/// Year-to-date revenue
	measure 'Revenue YTD' = TOTALYTD([Total Revenue], 'Calendar'[Date])
source: sales is warehouse.table('sales.orders') extend {
  measure:
    #(doc) Total revenue in USD
    # currency
    total_revenue is sum(amount)

  view:
    #(doc) Monthly revenue with a year-to-date running total
    revenue_ytd is {
      group_by:
        order_year is order_date.year
        order_month is order_date.month
      aggregate: total_revenue
      calculate: revenue_ytd is sum_cumulative(total_revenue) {
        partition_by: order_year
        order_by: order_month
      }
    }
}

Year-to-date is a property of a query over time, so it becomes a view rather than a measure.

Next Steps

On this page