Power BI: Debug DAX with TOJSON/TOCSV

Key insights

• TOJSON / TOCSV
  Convert a DAX table variable into a readable JSON or CSV string inside Power BI.
  TOCSV accepts a custom delimiter and a MaxRows argument (defaults to 10) to limit output size.
• How to use
  Temporarily replace your measure's RETURN with TOJSON(variable) or TOCSV(variable, delimiter, MaxRows).
  Evaluate the measure in a visual to see the table content, then restore the original logic when done.
• Debugging workflow
  1) Identify the suspect VAR table (FILTER, SUMMARIZE, ADDCOLUMNS).
  2) Wrap it with TOJSON/TOCSV and set MaxRows to a small number.
  3) Inspect rows, columns, and values; fix upstream logic and revert.
• Sampling and ordering
  Use TOPN or explicit sorting before TOJSON/TOCSV to preview the most relevant rows.
  Combine with simple ordering expressions for quick, targeted checks.
• Key advantages
  No external tools needed — work in Power BI visuals.
  Outputs show rows and columns clearly, control output size with MaxRows, and debug changes are temporary and reversible.
• Cautions and tips
  Keep MaxRows small to avoid performance issues; negative MaxRows may cause errors in some cases.
  Use VAR to cache expressions and remove debug wrappers before publishing.

Overview of the SQLBI Video

The YouTube video from SQLBI demonstrates how to debug DAX table variables by using TOJSON and TOCSV within Power BI measures. In the clip, the presenter shows how these functions serialize intermediate table expressions into readable strings so analysts can inspect content directly inside visuals. Consequently, viewers learn a non-invasive way to peek at virtual tables without leaving the Power BI environment. The approach is aimed at improving troubleshooting speed for complex measures that rely on virtual tables and multiple variables.

How TOJSON and TOCSV Work

Both functions convert a table expression into a single text value: TOJSON formats the table as a JSON string while TOCSV produces a CSV string where you can set delimiters and limit rows. Furthermore, they include an optional MaxRows parameter to limit output, which helps avoid heavy renders and keeps debugging focused on a subset of rows. For example, replacing a measure's final RETURN with TOCSV(variable, ",", 3) shows a concise preview of three rows from the intermediate table. This makes it easier to validate row counts, column values, and structure while the original calculation remains untouched.

Step-by-Step Debugging Workflow

The video outlines a simple workflow: identify the suspect table variable, replace the measure RETURN with TOJSON or TOCSV, evaluate the measure in a visual, then restore the original logic after inspection. Moreover, combining these functions with sorting or sampling functions such as TOPN helps surface the most relevant rows when dealing with large tables. The presenter also emphasizes using VAR to hold intermediate results, which makes the code cleaner and caching more effective during tests. After confirming the content, developers can revert to the original measure, avoiding permanent changes to production logic.

Practical Advantages and Use Cases

This technique removes reliance on external tools for basic inspection, which is especially useful when quick checks are required during report development. For instance, it shines when debugging time-intelligence calculations, ranking measures, or any DAX code that produces virtual tables via SUMMARIZE, ADDCOLUMNS, or nested filters. Additionally, readable JSON or CSV output is often easier to scan than long concatenated strings produced with CONCATENATEX, so the method reduces misinterpretation of complex datasets. As a result, developers can iterate faster and validate assumptions without switching contexts.

Tradeoffs and Challenges

Despite the benefits, there are tradeoffs to consider, mainly around performance and output limits. Serializing very large tables can still be slow or may not render fully in a visual, so developers must balance depth of inspection with responsiveness by using MaxRows or TOPN to sample. Furthermore, some users report issues when using negative values for MaxRows, and very long strings can be truncated or hard to read in the visual. Therefore, while this method is convenient, it is best used for targeted checks rather than full exports.

When to Use Alternatives

There are scenarios where external tools remain preferable, for example when performing deep audits, logging many iterations, or capturing full datasets for offline analysis. Tools such as DAX query profilers and dedicated editors offer richer logging and execution traces that are outside the scope of in-report serialization. However, for quick validation and stepwise debugging inside Power BI Desktop, the TOJSON/TOCSV approach provides a low-friction option that complements more advanced tooling. Therefore, developers should choose the method that best balances speed, detail, and resource use.

Best Practices and Final Takeaways

To get the most from this approach, the video recommends keeping debug code temporary and using sampling and sorting to reduce noise and performance impact. Moreover, combining serialization with clear variable naming and incremental checks helps isolate issues faster without creating confusing side effects. Finally, while TOJSON and TOCSV are powerful, they are diagnostic tools rather than replacements for good test design or external analysis when needed. Overall, the SQLBI video offers a practical, accessible technique that should be part of every Power BI developer's debugging toolkit.

Keywords

Debug DAX variables, TOJSON DAX, TOCSV DAX, Debug DAX with TOJSON, Debug DAX with TOCSV, Power BI DAX debugging, Inspect DAX variables, DAX TOJSON TOCSV examples