Seeking Optimization for A DAX Measure

[Anonymous]

Greetings.  I am hoping someone can help me optimize my DAX measure.  I am not using Power Query, nor am I using Power Pivot.  I am using a Pivot Table that has been added to the data model.  I am working with a table that has 44 columns.  The table in question (let's call it, "MyTable") has multiple uses, thus the number of columns in the output.  This is part of a template file that gets refreshed with SQL queries populating the table with data corresponding to two user given id's.  The first id corresponds to Ordinal 0 and the second corresponds to Ordinal 1.  For the given table, I want to show the difference between certain values corresponding to the two ordinals.  The table looks reminiscent of the following (except there's 44 columns).  I only populated the applicable data for my mocked up scenario:

Ordinal

ID

<other>

<other>

<other>

<other>

Area

<other>

<other>

<other>

Price

<other>

<other>

<other>

0

11111

…

…

…

…

Area1

…

…

…

4938.56

…

…

…

0

11111

…

…

…

…

Area2

…

…

…

382.49

…

…

…

0

11111

…

…

…

…

Area3

…

…

…

1080.78

…

…

…

1

22222

…

…

…

…

Area1

…

…

…

278.56

…

…

…

1

22222

…

…

…

…

Area2

…

…

…

1298

…

…

…

1

22222

…

…

…

…

Area3

…

…

…

1389.22

…

…

…

The following is an example of my DAX code.  I am aiming to get the difference in price between the two ordinals to show for each Area.  I have multiple measures that look similar to this.  The difference between them all is that they correspond to different Price columns.  While the code works, it can just take a long time to run whenever the table is heavily populated.  Note, I need the DAX to not generate an error if and when the table is empty after being refreshed:

=IF(CALCULATE(COUNTROWS(MyTable),FORMAT(MyTable[ORDINAL],"Standard")<>"")=0,0,

var Price2= CALCULATE(SUMX(MyTable,VALUE(MyTable[Price])),VALUE(MyTable[ORDINAL])=1)

var Price1= CALCULATE(SUMX(MyTable,VALUE(MyTable[Price])),VALUE(MyTable[ORDINAL])=0)

RETURN ROUND(Price2-Price1,2)

I was thinking I could utilize SUMMARIZE(), ADDCOLUMNS(), or even SUMMARIZECOLUMNS() to hold the applicable columns in a variable (virtual table) but, apparently, I can't figure out how to get it to work without generating an error or getting zeroes for my results.  If my code is optimized as is, then I will just have to brainstorm on a whole other approach.  Hopefully, someone can help.  Let me know if anything else is needed in order to assist.

[Anonymous]

I have come up with the following DAX.  Note, that I have replaced "MyTable" from my original post with "DetailData" (the actual name of my table) and am using MyTable as a variable name.  Also, "Price" is "SERVICE_PRICE".  The following code appeared to be a little faster with my using SUMMARIZE within FILTER instead of the FILTER within SUMMARIZE.  Remember, the DAX has to accommodate situations when the table is empty.  For some reason, I need to use FORMAT on the Ordinal column for MyTable instead of VALUE, otherwise it doesn't successfully capture when my table is empty.  Next, the values do not show correctly when I leave DESCRIPTION out of the SUMMARIZE function.  I did try to use SUMX within my SUMMARIZE funtion, as well with ADDCOLUMNS but, referencing that field from MyTable wasn't working.  I either got the wrong value or nothing at all.  Perhaps someone can show me how?  As for the last line, I am using this DAX in two tables.  One table is to show the SERVICE_PRICE under each ordinal and the other is to show the Difference between the two.  I cannot use the pivot table function to show the Difference because I have more than one Row Label in place and it will not work properly when that is the case.  So, that is why I am using two pivot tables and why I am multiplying by -1 when I don't have Ordinal1.  The side of the table with Ordinal0 would otherwise show negative values if I don't.  I feel like the following DAX could still be faster but, this is at least heading in the right direction.  If anyone has any ideas on how to improve it and/or has any questions about it, please let me know.  Meanwhile, if I figure anything else out, I will reply again.  Thanks, in advance.

=var MyTable = FILTER(SUMMARIZE(DetailData,DetailData[Ordinal],DetailData[DESCRIPTION],DetailData[SERVICE_PRICE]),FORMAT(DetailData[ORDINAL],"Standard")<>"")

RETURN IF(COUNTROWS(MyTable)=0,0,

var Ordinal1 = SUMX(FILTER(MyTable,VALUE([Ordinal])=1),VALUE([SERVICE_PRICE]))

var Ordinal0 = SUMX(FILTER(MyTable,VALUE([Ordinal])=0),VALUE([SERVICE_PRICE]))

RETURN ROUND((Ordinal1-Ordinal0)*IF(ISBLANK(Ordinal1),-1,1),2))

[AlexisOlson]

If you make sure that your Price column is in a numeric format (not text), then I think you can make your code faster and simpler like this

Diff =
VAR Price0 =
    CALCULATE ( SUM ( MyTable[Price] ), MyTable[Ordinal] = "0" )
VAR Price1 =
    CALCULATE ( SUM ( MyTable[Price] ), MyTable[Ordinal] = "1" )
RETURN
    ROUND ( Price1 - Price0, 2 )

[Anonymous]

Thanks for responding.  Unfortunately, my DAX started out that simple but was not successful.  Formatting the column on the table has no impact.  If the table is ever empty, I get an error about the calculation not being able to use value type string when a number is expected, suggesting that I utilize VALUE or FORMAT to address the issue.  FYI - I am using the IF statement to help accommodate the empty table scenario by defaulting to zero if the number of rows where the Ordinal is not blank is equal to zero.  This was better than using the IF condition with each variable but, looks like there is still plenty of room for optimization.

[AlexisOlson]

Something is wrong with your setup if you need to be using VALUE for the Price column. If it's already a numeric data type, then I can't tell what the problem is.

The Ordinal column should probably be a numeric data type too, in which case you'd want to drop the quotes I had in my suggested measure.

[Anonymous]

That's the dilemma (or at least part of it).  While the table is populated, there are numeric values in the corresponding fields.  Again, I need DAX to be able to handle the table whenever it is empty, and even though I format the entire columns where the table is, an empty column is treated as string.  

[AlexisOlson]

That's strange. An empty numeric column should return BLANK(), not an empty string.

In any case, there's something odd going on with your setup and your question cannot likely be resolved without figuring out what that is. The easiest way for someone to figure it out would be if you shared your file but if that's not possible, then you have to do the best you can to give the relevant context some other way.

[Anonymous]

I started a blank Excel document, put together a dummy table with just three columns and tried to simply use SUM(), like you have above, and it works fine until I empty the table.  Then I get the message about comparing a string value where an integer type is expected.  Perhaps there is something else I need to do to format my table's column that I don't know about?  I have tried highlighting the entire column and choosing Number format with 2 decimal places.  The formatting never goes away, even when I empty the table but, DAX views it as a string when it's empty.  Any thoughts on how to properly format the table?