Latest Enterprise DNA Initiatives

Best Practice Question - Cost Report

DAX Calculations
#1

Good morning to all -:slight_smile:

Apologies if my question looks trivial - I am totally inexperienced PBI user.

Lets assume I want to create a cost report / visualization. My data structure is similar to uploaded as an example - there is a “date” column, “account” which is at the highest granularity (linked to the chart of accounts via “accountID”), where combination of characters is meaningful, and “value” which is an entry into accounting system - combined with “side” it shows whether this particular value is an expense or revenue. The data is being extracted from SQL database.

Now, what is the best way to tackle the aggregations of “Account” column:

  • say, I want to show the values for first three characters in Account column (which supposedly should aggregate all underlying values) - this is only an example, e.g. first three+last two is also meaningful, and as you might guess some more.

  • should I then :

  • create set of measures - and show them separately, and then branch from there,

  • create calculated columns - for every meaningful character combination,

  • create DAX formulas - to filter for every meaningful character combination.

Would be delighted to hear from you -:slight_smile:

Best

RafalData_example.xlsx (10.2 KB)

#2

@Rafal_J,

The wonderful and maddening thing about Power BI is that there are very few absolutes – to almost every question like this, the answer is “it depends on what you want to do with the data”. However, based on what you described here it sounds like you may want to include the substrings from the Account column as either axes in visualizations and/or as values in slicers. If that’s the case, I would say the answer to your question is “(d) none of the above” - I would recommend creating these substrings/concatenations in Power Query. That will give you all the benefits of a calculated column (from the slicer/visualization standpoint), but also provide better performance.

While not an absolute, I find a good rule of thumb to be this: if it can be done in Power Query (meaning that it doesn’t need to be dynamic within the context of a report session) it probably should be. The corollary to this rule of thumb is that calculated column will rarely be the correct answer – if it needs to be dynamic, use measures, if not use Power Query.

Enterprise DNA expert and resident DAX maestro @AntrikshSharma succinctly states it this way: SQL > PQ > DAX

I hope that’s helpful.

  • Brian
2 Likes
#3

@BrianJ
Thanks, that helps.
I will experiment with transformations in PQ then -:slight_smile:

Best

Rafal

#4

@BrianJ

May I have an additional question ?

Say I have 3 columns; “symbol” which might have repetitive values of different length and structure, “description” with data as above; now I would need to replace text in “description” labelled “IncorrectString” with a text labelled “CorrectString”; column “segment” indicates which string is correct and which is not. Both “correct” and “incorrect” correspond to the identical string in column “symbol”.

How do I tackle that ?

Best
RafalData_example_2.xlsx (9.8 KB)

#5

@Rafal_J,

I have a really cool way of handling this that I learned a while back. However, before I craft and post that solution, can I ask you to please start a new topic, just copying your new question over into that post?

This is for search/archival purposes - we try to have each fundamentally distinct question in its own thread. Also, you’ll often get a faster response doing it this way, since those of us who respond to forum posts typically look for open questions first, not follow-ons to solved posts.

Thanks!

  • Brian
#6

Thank you for the suggestion - I did that and intend to proceed accordingly from now on.

Best

Rafal

1 Like