New Column

[th340385]

Hello - I currently have a chart of accounts table that includes financial statement roll up description at the bottom of the same table (see screenshot below). I am trying to include a new column (blue) that includes the financial statement roll up description for each account. Any guidance on how to accomplish this would be appreciated! 

Account #	Description	Totaling Range	New Column Expectation
100000	Cash 1		= Total Cash and Cash Equivalents
100001	Cash 2		= Total Cash and Cash Equivalents
100002	Cash 3		= Total Cash and Cash Equivalents
193000.1	Land		= Total Fixed Assets
193000.2	Land Improvements		= Total Fixed Assets
193000.25	Accum. Depr. - Land Improvements		= Total Fixed Assets
193000.3	Building		= Total Fixed Assets
193000.35	Accum. Depr. - Buildings		= Total Fixed Assets
100999	Total Cash and Cash Equivalents	099999.000..100999.000
196999	Total Fixed Assets	193000.000..196999.000

[v-lionel-msft · Accepted solution]

Hi @th340385 ,

1. Split [Totaling Range] column

Then you can get such a table.

2. Create a table like this.

Table = 
SUMMARIZE(
    FILTER(
        Sheet1,
        Sheet1[Totaling Range1] <> BLANK()
    ),
    [Account #], [Description], [Totaling Range1],[Totaling Range2]
)

3. Create a measure like this.

Measure 2 = 
VAR x = 
SWITCH(
    TRUE(),
    MAX(Sheet1[Account #]) >= MIN('Table'[Totaling Range1]) && MAX(Sheet1[Account #]) <= MIN('Table'[Totaling Range2]), MIN('Table'[Description]),
    MAX(Sheet1[Account #]) >= MAX('Table'[Totaling Range1]) && MAX(Sheet1[Account #]) <= MAX('Table'[Totaling Range2]), MAX('Table'[Description])
)
RETURN
IF(
    CONTAINSSTRING( MAX(Sheet1[Description]), "Total"),
    BLANK(),
    x
)

(Note: Do not establish a relationship between the two tables.)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@th340385 , you have write logic using switch true and containsstring

https://blog.enterprisedna.co/using-switch-true-logic-in-power-bi-dax-concepts/

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

https://docs.microsoft.com/en-us/dax/search-function-dax

[th340385]

Can you please provide an example? 

[jthomson]

Just seems like a simple if statement to me

[th340385]

Can you please provide example?