What is the DAX equivalent?

[Jawed]

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"
                    )
                )
            )
        )
    )
)

[v-shex-msft · Accepted 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

[Jawed]

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.

[Jawed]

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!