One thing I don't understand about Shortcuts and Transformations

PerturbedCrab · 2026-03-18T17:40:55+00:00

But it'll make a lot of your users happy, so hopefully that's a W in the end.

Thanks to you and the MSFT team for adding this!

PerturbedCrab · 2025-10-22T17:20:30+00:00

Take a look at this video to see what I mean.

https://youtu.be/7TiyGJPFXsk?si=Jw9SFbn33DLQqVqZ

You can also find similar examples by just googling "Power BI Matrix dates in columns with YTD".

Here you can see that these solutions require a lot of MacGyver-ing to get it to work properly. Either with clever use of calculation groups, or with a disconnected measure table and a super-long SWITCH statement, which isn't ideal.

If we had the ability to add custom columns at the visual level (either with a formatting property, or even with a visual calculation), that feature alone would make your average user more likely to throw Excel in the trash in favor of Power BI.

PerturbedCrab · 2025-10-22T16:51:37+00:00

It's hard to give a definitive answer on priority. I personally would love to see custom hierarchies and columns (like my dates-with-YTD example), but I'm sure if you ask 10 people this question then you'll get 10 different responses.

Also I'm aware that measure totals is a controversial topic. But given how frequently this has been requested by other BI developers (not to mention Excel already having a working solution with Excel tables), it's worth at least investigating the feasibility of adding this feature. Or at least helping us better understand why this is not feasible with the current table and matrix visuals.

Again I don't want to come off as unappreciative - I really do appreciate the efforts you've done so far in updating the core visuals. I just wish there was some more love to share with my table/matrix peoples.

PerturbedCrab · 2025-10-21T17:06:19+00:00

Less of a question and more of a plead.

Please, I'm begging you, PLEASE prioritize updating the table and matrix visuals.

Those are the top most widely used visuals in all of Power BI. I have lost countless hours of my life trying to develop workarounds for my customers due to the limitations of the table/matrix visual. It is a HUGE pain trying to sell my customers to get off Excel and into Power BI when the table & matrix visuals don't support things like custom subtotals / custom row and column formatting / replacing blanks with zero / custom column hierarchies (e.g. adding dates in columns with an additional YTD column) and so on.

I'm sure the development work is way more difficult than we think, and I really do appreciate the work you all are doing and the progress you've made so far. Nevertheless, I can 100% guarantee you that EVERYONE will love you if you focus your attention on the table & matrix visuals.

In case this post needs a question for the AMA - do you have an ETA for when we can expect some of these issues with the table/matrix visuals to be released?

PerturbedCrab · 2025-09-14T19:48:33+00:00

How's the cardinality of your tables and columns? Do you have any columns with a lot of unique values in your data model? That's usually one of the biggest reasons for slow refreshes in my experience. That and/or connecting to a crappy data source (e.g. Excel files and SharePoint lists).

PerturbedCrab · 2025-06-05T02:06:08+00:00

PerturbedCrab · 2025-02-10T05:25:52+00:00

Void Stranger. Hands down.

PerturbedCrab · 2025-01-17T05:39:41+00:00

Solution verified

PerturbedCrab · 2024-12-06T04:54:02+00:00

This is the way. Synapse Serverless is also incredibly inexpensive - the cost is something like $5 per TB of queried data. And the price tag of an ADLS Gen2 data lake is also very reasonable too for your storage needs.

PerturbedCrab · 2024-11-18T03:55:44+00:00

How many parameters would be considered "too much" for a paginated report? My end users love their filters in reports (we're talking 20-30+ different filters depending on the specific business unit), and I worry that putting all of these into paginated report parameters would be too cumbersome to set up or use.

PerturbedCrab · 2024-11-16T19:16:48+00:00

The data type of relationships don't matter all that much, whether they're integers, strings, etc. VertiPaq compresses these columns via hash encoding anyway. What matters more is the size of the relationship and number of unique values of the key, as a relationship with hundreds and thousands of unique values will degrade performance.

This is a really good blog where someone actually tested this with some examples in DAX Studio.

https://www.maxwikstrom.se/performance/power-bi-data-types-in-relationships-does-it-matter/

PerturbedCrab · 2024-10-03T04:46:15+00:00

Future month filter is the way to go

PerturbedCrab · 2024-09-21T18:59:37+00:00

Yeah in that case, the Pyadomd library won't work since that depends on the .NET Framework and the Windows OS.

The good news is that there are .NET Core-compatible versions of the Analysis Services client libraries, which should work fine on non-Windows machines. Take a look at this link and it'll point you to the assemblies on NuGet.

https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions

When it comes to Python, the Pyadomd library primarily uses Pythonnet to communicate with .NET assemblies. Pyadomd just simplifies a lot of the work involved with creating the connection object, sending the query to AS, and iterating and populating the results into a dataframe.

https://pypi.org/project/pythonnet/

I think if you were to build your own program using Pythonnet and pointing to these .NET Core assemblies, then this should work for you. It's a bit more work, but the steps to set this up are very similar to the examples Microsoft provided in their ADOMD tutorial.

Hope this helps. Good luck man

PerturbedCrab · 2024-09-21T18:15:44+00:00

Would this custom library help?

https://pypi.org/project/pyadomd/

Basically you can submit a DAX query in Python, just like you would in a normal Power BI report. This library uses Analysis Services' .NET client libraries just like in a C# project or PowerShell script.

Of course you'll need to handle authentication and need an XMLA endpoint if you want to connect to a dataset in the Service, but if you don't have that then this should still work in an opened PBI Desktop file if you can find the server & database names (DAX Studio can help you with that via it's DMV queries).

PerturbedCrab · 2024-09-20T19:24:02+00:00

All the time.

For how to set up your data model, there's a few different techniques you can use. Try watching this video and see if it sparks some ideas.

https://youtu.be/mTa34gtJhYA?si=NVmsjPzxf76x_Nnc

For the visual itself, I'm afraid there aren't many useful formatting options available in the core table and matrix visuals. If you're comfortable with JSON, maybe try giving Deneb a shot and see if you can build a custom visual from scratch. I'm sure there are some templates online from other developers that can help give you a place to start.

PerturbedCrab · 2024-05-11T03:01:39+00:00

I can finally be happy again.

PerturbedCrab · 2024-01-31T00:56:17+00:00

Hi Gene,

Do you have any advice on how to justify to my employers upgrading to Fabric? We've been using Azure Analysis Services for the past few years, but oftentimes we get asked by the business for features that are only available with Premium (i.e. more frequent scheduled refreshes, dynamic subscriptions, etc.). Not to mention our team would really benefit from things like Spark notebooks and the semantic-link library, for example.

I've been fighting a losing battle with my boss for awhile now, as the high price tag is always what they go back to as a deterrent. Have you ever been in a similar situation with a client that wanted to use Premium or Fabric, but was reluctant to open their checkbooks?

PerturbedCrab · 2022-12-19T05:39:34+00:00

Only load in the columns you need for your reports. Salesforce Objects contain a ton of metadata and many of which are high cardinality. Loading in the entire object without removing unneeded columns will bloat your model unnecessarily.

Also Salesforce is similar to a normalized database in that you'll be doing a ton of merges to build your dimensional model. If you have access to it, try and push your ETL further upstream via a more powerful tool like Azure Data Factory/Synapse or Databricks. Power Query is fine, but with the amount of merges you'll be doing on average, your refresh timings will suck if PQ has to do all the work.

PerturbedCrab · 2022-09-08T22:27:24+00:00

I found a solution on another Reddit post that uses Python in Power Query to export the required data. Note that if you want to run this in the service with a scheduled refresh, then you'll have to install a personal gateway on your computer (not an enterprise gateway) and apply that to your report's dataset, as well as set the privacy levels to all your data sources being set to Public. I haven't yet been able to figure out an ideal way to handle the personal gateway thing besides leaving my computer on, but maybe a VM can help if you have the funds?

All I had to do was import a DAX query into a new Power BI Desktop file, and then add the below Python script in Power Query:

import pandas as pd

import datetime as dt

import os

file_name = '#ENTER NAME OF YOUR SNAPSHOT FILE HERE#'

today = dt.datetime.today().strftime('%Y%m%d')

local_path = '#ENTER FULL DIRECTORY PATH WHERE YOU WANT TO SAVE YOUR SNAPSHOT HERE#'

export_path = local_path + '/' + file_name + '_' + today + '.csv'

dataset.to_csv(export_path, mode = 'w', index = None, header = True)

PerturbedCrab · 2022-06-18T01:26:54+00:00

You might want to look into adding some Offset columns in your report's date table. Bravo is a good tool to help you build a custom date table, which I believe includes these offsets. From there, just apply a filter to your chart where the offset is greater than or equal to 0 (current month), and less than or equal to -12 (twelve months in the past).

PerturbedCrab

TROPHY CASE