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.
Hi everyone,
Despite the fact that I am new to Power BI, I am really enjoying the experience. Any way, I am using the below measure which gives me the result I want. However, I believe there may be a much better and efficient way of getting to the same result. I have my TB and it is showing my Chart of Account codes at the most granular level. I have asked it to categorise each of them in terms of whether they are a fixed asset, current asset, Current liability, etc. Below is the formula and I would be grateful if you could suggest a better DAX equivalent to my old style Excel functions that I have brought with me to Power BI. Thank you,
Category detail =
IF (
AND ( TB[BS Category] = "3.Assets", LEFT ( TB[Subaccount Name], 2 ) <= "10" ),
"3.2Fixed Asset",
IF (
AND ( TB[BS Category] = "3.Assets", LEFT ( TB[Subaccount Name], 2 ) > "10" ),
"3.1Current Asset",
IF (
AND (
TB[BS Category] = "4.Liabilities",
LEFT ( TB[Subaccount Name], 2 ) = "23"
),
"4.2Long-Term Liability",
IF (
AND (
TB[BS Category] = "4.Liabilities",
LEFT ( TB[Subaccount Name], 2 ) <> "23"
),
"4.1Current Liabilities",
IF (
AND ( TB[BS Category] = "5.Equity", LEFT ( TB[Subaccount Name], 1 ) = "3" ),
"5.1Reserve",
IF (
AND ( TB[BS Category] = "5.Equity", LEFT ( TB[Subaccount Name], 1 ) >= "4" ),
"5.2CY Op.Surplus"
)
)
)
)
)
)
Solved! Go to Solution.
Hi @Jawed,
Your formula seems like calculated column formula, did you means to convert them to measure version?
If this is a case, you can take a look at below formula:
Measure:
Category detail(measure) = VAR current_category = LASTNONBLANK ( TB[BS Category], [BS Category] ) VAR current_subName = LASTNONBLANK ( TB[Subaccount Name], [Subaccount Name] ) VAR Sub_Name = VALUE ( LEFT ( current_subName, 2 ) ) RETURN SWITCH ( current_category, "3.Assets", IF ( Sub_Name <= 10, "3.2Fixed Asset", "3.1Current Asset" ), "4.Liabilities", IF ( Sub_Name = 23, "4.2Long-Term Liability", "4.1Current Liabilities" ), "5.Equity", IF ( Sub_Name >= 4, "4.1Current Liabilities", IF ( Sub_Name = 3, "4.2Long-Term Liability" ) ) )
In addition, I don't think you can use logic symbol ">=, <=, >, <" to compare with string values.("=" and "<>" support compare with string value)
For your situation, you need to convert them to numeric value before use these symbol.
Sample:
Category detail = VAR Sub_Name = VALUE ( LEFT ( TB[Subaccount Name], 2 ) ) RETURN SWITCH ( TB[BS Category], "3.Assets", IF ( Sub_Name <= 10, "3.2Fixed Asset", "3.1Current Asset" ), "4.Liabilities", IF ( Sub_Name = 23, "4.2Long-Term Liability", "4.1Current Liabilities" ), "5.Equity", IF ( Sub_Name >= 4, "4.1Current Liabilities", IF ( Sub_Name = 3, "4.2Long-Term Liability" ) ) )
Regards,
Xiaoxin Sheng
Hi @Jawed,
Your formula seems like calculated column formula, did you means to convert them to measure version?
If this is a case, you can take a look at below formula:
Measure:
Category detail(measure) = VAR current_category = LASTNONBLANK ( TB[BS Category], [BS Category] ) VAR current_subName = LASTNONBLANK ( TB[Subaccount Name], [Subaccount Name] ) VAR Sub_Name = VALUE ( LEFT ( current_subName, 2 ) ) RETURN SWITCH ( current_category, "3.Assets", IF ( Sub_Name <= 10, "3.2Fixed Asset", "3.1Current Asset" ), "4.Liabilities", IF ( Sub_Name = 23, "4.2Long-Term Liability", "4.1Current Liabilities" ), "5.Equity", IF ( Sub_Name >= 4, "4.1Current Liabilities", IF ( Sub_Name = 3, "4.2Long-Term Liability" ) ) )
In addition, I don't think you can use logic symbol ">=, <=, >, <" to compare with string values.("=" and "<>" support compare with string value)
For your situation, you need to convert them to numeric value before use these symbol.
Sample:
Category detail = VAR Sub_Name = VALUE ( LEFT ( TB[Subaccount Name], 2 ) ) RETURN SWITCH ( TB[BS Category], "3.Assets", IF ( Sub_Name <= 10, "3.2Fixed Asset", "3.1Current Asset" ), "4.Liabilities", IF ( Sub_Name = 23, "4.2Long-Term Liability", "4.1Current Liabilities" ), "5.Equity", IF ( Sub_Name >= 4, "4.1Current Liabilities", IF ( Sub_Name = 3, "4.2Long-Term Liability" ) ) )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft. I have now gone ahead and created the measure and it works fine. However, I wanted double check something. Do you mean that I should go to Query Editor and change the format of the string to value and then use the function? At the moment, the measure seems to be working fine.
HI @v-shex-msft,
Thank you so much for your suggestion. Yes, it is a calculated column as I wasnt sure of any other way. Let me try it out and get back to you.
Cheers!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |