Best practice for creating a index column using new colum in table view using dax

DAX DAX Calculations
1

HI simple one im sure but i cant find the best answer for this.

Whats the easiest way to create a index column using dax formula in the table view? not using query editor.

I have created a look up table of short months using and want to create the index column for it using dax.

Short Months = VALUES( '2016 Product Budgets'[Short Month] )

Capture
Capture1175Ă—557 30.2 KB

Thanks

2

@Krays23,

Lot of different ways you can do this. You could use RANKX, calculated column using COUNTROWS/ EARLIER, etc. I’m not sure that there is a general best practice here – would seem to depend on what you’re indexing.

Here’s a really simple way to do short month:

DAX Index =
SELECTEDVALUE( Dates[MonthOfYear] )

image

I hope this is helpful.

– Brian

3

Thanks i cant get it to show any result on my column. Sorry Brian i did say im trying to learn :frowning:

Dans Advanced demo model - Advanced Data Transformation and Modeling.pbix (784.2 KB)

4

@Krays23,

No worries. I did mine is a measure, so when you’re doing a calculated column you don’t need SELECTEDVALUE, since the calculated column by nature provides row context. This is also why it’s helpful to see the PBIX file with the data model. Here’s one way to do the index based on your Dates table and data model:

DAX Index = 

LOOKUPVALUE(
    Dates[Month Of Year],
    Dates[Short Month],
    'Short Months'[Short Month]
)

image

However, given that it looks like you’re going to be using this short month table to filter other tables, I would recommend creating it in Power Query, rather than DAX to avoid potential circular reference problems down the road.

  • Brian
2 Likes
5

Thanks Brian.

That was my next question as when you create tables in the table view via dax u don’t see them in the power query whats the difference? Is better to do them via dax does this save on file size ? If not I’d always prefer to create my tables in power query i think ? What’s your preference?

Cheers

Dan

6

That’s correct – tables created via DAX expressions are not accessible via Power Query.

I wouldn’t worry about file size in this case. The VertiPaq compression engine in Power BI is extremely efficient, and so any table that you could create via DAX that would run at an acceptable performance level is unlikely to have a large impact on file size. This is a gross oversimplification, but not a bad general rule - “if you can do it in Power Query you probably should…”

Doing more “upstream” in Power Query will improve performance and also simplify your DAX, making it easier both for you and for anyone reviewing your model/report.

@AntrikshSharma puts it this way, which I like - SQL > Power Query > DAX.

  • Brian
3 Likes