Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jawed
Helper III
Helper III

What is the DAX equivalent?

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.