Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
@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
Can you please provide an example?
Just seems like a simple if statement to me
Can you please provide example?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
98 | |
76 | |
66 | |
59 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |