Top N of a Virtual Table

DAX DAX Calculations
1

I have a table that has Average Temperatures for every day. I created a Standard Deviation measure. Which I am showing at the month level. How can get what Month had the TOP standard deviation automatically. You can clearly see that it’s october.

Measure:
St Dev = STDEV.P(Temperatures[Avg Temp F])

image
image1024×589 98.6 KB

image
image790×1049 121 KB

Thanks In Advanced - Enterprise DNA Community.

2

@mbraun,

It’s always easier to do this with a PBIX file provided, but let me give it a go anyway.

Top StDev =

VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Temperatures, Dates[Monthname], Temperatures[Average Temp] ),
        "@StdD", [Stdev]
    )
VAR TopRow =
    TOPN ( 1, vTable, [@StdD], DESC )
VAR Result =
    MAXX ( TopRow, Dates[Monthname] )
RETURN
    Result

I’m guessing at some of the field names, but this should give you the gist.

If this doesn’t work, please provide a PBIX so that I can get you a precise, tested solution.

I hope this is helpful…

– Brian

3

Hi there,
Please find TopN DAX Function Syntax as below:

image
image939×160 7.23 KB

https://info.enterprisedna.co/dax-function-guide/topn/

4

Thanks @BrianJ . One other thing. Is it worth it to test what the virtual table will look like by creating the table. (See Picture). What is your approach? If you are going to do multiple calculations on that virtual table is it worth it to create the table vs constantly creating a VT in the measure?

T.I.A.

image
image965×331 44.4 KB

5

@mbraun,

When working with virtual tables, I strong recommend working in DAX Studio or Tabular Editor so that you can run DAX queries to visualize your virtual tables without creating a bunch of physical tables.

I’ve been fortunate to be a participant in the TE3 beta and when that comes out for wide release at the end of this month, I can’t recommend it highly enough. Truly a beautiful and incredibly powerful integrated development environment (finally!) for DAX.

Check out this video I did a couple of weeks ago - talks about some of my best practices for working with virtual tables, including addressing your Q above. Also, provides a glimpse at TE 3.

  • Brian
6

@BrianJ - Thank you!!

7

@BrianJ Just watched the YouTube - Beyond straight forward. I sometimes conceptualize the idea of what I want to do in my head. Actually figuring out doesn’t work. I will certainly use DAX Studio and Tabular Editor.

Thanks to you and the rest of Enterprise DNA Top Notch.

1 Like