DAX Measure is always variant type even when it shouldn't be?

[alexeisenhart]

I started rebuilding a 1000+ measure PBIX so that I can take advantage of Calculation Groups. Let's call this file Model 3. I created calculation groups and measures via Tabular Editor, which I also recently discovered. (Noting this in case I have shot myself in the foot somehow.)

I got a few hours into this file and then discovered that every measure that I create has formatting options disabled, even if the measure is a hardcoded value. When I inspect the measure in Tabular Editor the data type is Variant.

The more complex measures have a greyed-out format string, but I can still adjust with the buttons, such as in the following image. Measures that follow this pattern are coming up as variants as well.

I've tried to reproduce this strange behavior by making a new PBIX file -- call it Test -- creating a blank dataset, and then creating a measure and a calculation group via Tabular Editor, but I can't reproduce it.

I also copied a dataset from Model 3 to Test by copying the M code and then manually created a measure in Test that is identical to the measure in Model 3... in Model 3 it's variant, in Test it's Integer.

If a calculation group has a Format String Expression configured then I know that there are invisible measures created to handle formatting. I haven't used Format String Expression with this file.

I know that I can use the FORMAT() function, but I don't want the measures to be returning Text or variants in the first place.

Why would a simple measure such as "Measure = 1" return a variant type? Is there something broken about the Model 3 PBIX file?

[TomMartens]

Hey @alexeisenhart ,

I can't reproduce your issue, the screenshot below shows the measure "New measure". As you can see I can choose the data type:

I'm using Tabular Editor version 2.11.6 and Power BI Desktop version 2.83.5894.661 64-bit (July 2020).

Regards,

Tom

[alexeisenhart]

Thanks Tom! I'll try to slim down my Model3 file and upload a sample. I can't recall... what's the preferred way to share PBIX files? Is a dropbox link okay?

[alexeisenhart]

It seems to be caused by the calculation group?

I have a sample file: https://www.dropbox.com/s/p8hhj3ts802ocsl/Sample.pbix?dl=0

This was created by removing all datasets from Model3, but I retained the calculation group: it does not need to be refreshed, but it's now invalid because it is referencing datasets that are deleted. All measures are variant type, but when I delete the calculation group then I can assign a format to the measures. Is this just a detail about how calculation groups operate?

I did another test with a fresh file:

1. Create a calculation group
2. Create a calculation item within the group that returns text and a 2nd item that returns an int

This resulted in the "Format" dropdown being unavailable, but the buttons ($, %, etc) were available.

If I deleted one of the calcuation items so that the calculation group always returned the same data type then there was no apparent change. If I deleted both calculated items and created a new one that just returned SELECTEDMEASURE() then the Format dropdown was available for all measures again.

I'm running the latest tabular editor release, 2.11.7499.21291.

[TomMartens]

Hey @alexeisenhart ,

I'm wondering about the version of Tabular Editor, as this is the latest release:

Hmm, besides that, I can reproduce the problem in the sample file you provided.

Next to this, I have to admit that I have no idea, what the expected behavior should be.

As I consider calculation groups being a single-columned table used as an axis to all the available measures.

For this I would expect that I can not change the data type using the measure tool.
I think that using a constant value instead of using SELECTEDMEASURE() inside a calculation item will return the same value for all measures at once. I assume that this will overwrite/lock the ability to change the data type of the base measure.

I will investigate this in more detail in the upcoming days.

Regards,

Tom

[alexeisenhart]

Hi @TomMartens,

I feel a little better that this stumps you too! Thanks for your help. I'll do additional reading and testing and share anything that I discover.

Regarding the Tabular Editor version, I think we're both on the same version: I was reporting the version that comes up in the preferences dialog.

[TomMartens]

Hey @alexeisenhart ,

can you please provide me with some hint, why you want to create calculation groups without using SELECTEDMEASURE().

I use a similar approach using more than one calculation group, but this is to provide a more fine-grained control to the user how to calculate the measure.

Regards,

Tom

[alexeisenhart]

Hey @TomMartens.

Actually, I do want to use SELECTEDMEASURE() in the calculation groups, I was only testing with constants and other variations to try to determine why I can't set the Format property on measures.
Here's what I'm trying to accomplish...

In my current analysis package that I'm using in production today, Model 2, I have a page that has a grid of measures: it displays a list of base measures with various groupings.

For example, I might calculate and display:

1. the total cases shipped
2. the average cases shipped per day
3. the max cases shipped per day
4. the max to average ratio
5. the number of cases on the peak day

And so on. This is implemented with a grid of Card visuals. So for one base measure (cases shipped) I have 5 cards and 5 measures (1 base measure + 4 with SUMMARIZE or other expressions). I have about 12 base measures, so that's a grid of 60 Card visuals and 60 measures.

I think Calculation Groups were created to solve this exact problem.

I want to avoid using the Calculation Item's Format String Expression due to all of the hidden "virtual" measures that it creates to do the formatting. I think I should avoid this because I expect there will be scalability issues, as this analysis file is very large (1000+ measures is still likely).

I'm flexible and open to alternate ideas, but I was not expecting to lose the ability to assign a Format for my measures, so that alarmed me, especially because I don't understand why it happened.

EDIT: Spelling

[alexeisenhart · Accepted solution]

Hey @TomMartens!

I've been doing a lot of reading and I think I've run into Power BI bugs described here: https://github.com/otykier/TabularEditor/issues/380 .

I've run into a handful of other formating issues with other visuals which I can trace back to Calculation Groups.

Thanks for your help, but I think I will have to wait to use Calc Groups until this issue is resolved by Microsoft with a future release.

[TomMartens]

Hey @alexeisenhart ,

dropbox link or onedrive are okay, as both make sure that files do not contain fancy things.

Make sure that your pbix does not contain private information.

Regards,

Tom