Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Transform Excel Command to DAX or M Language

Hi,   I created this command but need help to transform it to Dax or M Language.   Thanks...tksnota...   (CASE WHEN transtype IN('N','C','P','F') AND (((gbkmut.transsubtype NOT IN ('R','S') A...
Need Help
_AAndrade's avatar
_AAndrade
Resident Rockstar
1 year ago

Hi Anonymous,

Try this DAX measures:

Debit = 
IF(
    [transtype] IN {"N", "C", "P", "F"} &&
    (
        ([transsubtype] NOT IN {"R", "S"} && [bdr_hfl] >= 0) ||
        ([transsubtype] IN {"R", "S"} && [bdr_hfl] < 0)
    ),
    [bdr_hfl],
    BLANK()
)

Credit = 
IF(
    [transtype] IN {"N", "C", "P", "F"} &&
    (
        ([transsubtype] NOT IN {"R", "S"} && [bdr_hfl] >= 0) ||
        ([transsubtype] IN {"R", "S"} && [bdr_hfl] < 0)
    ),
    BLANK(),
    -[bdr_hfl]
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,

 

I want 2 columns created namely Credit and Debit taken from the amount based on the conditions stipulated on transtype and transsubtype 

 

Hope I make myself clear...

 

Thanks...tksnota...

 

 

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

    My last post might solve your issue.
    If not please give more details and share how is your table and the disered output.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I tried your dax but i encountered these problems as shown.

       

      I only attempted to use one column for debit...

       

       

      • _AAndrade's avatar
        _AAndrade
        Resident Rockstar
        1 year ago

        Please try this one

        CalculatedTable = 
ADDCOLUMNS(
    'OriginalTable', // Replace 'OriginalTable' with the name of your table
    "Debit", 
    IF(
        CONTAINSROW({ "N", "C", "P", "F" }, [transtype]) &&
        (
            (NOT CONTAINSROW({ "R", "S" }, [transsubtype]) && [bdr_hfl] >= 0) ||
            (CONTAINSROW({ "R", "S" }, [transsubtype]) && [bdr_hfl] < 0)
        ),
        [bdr_hfl],
        BLANK()
    ),
    "Credit", 
    IF(
        CONTAINSROW({ "N", "C", "P", "F" }, [transtype]) &&
        (
            (NOT CONTAINSROW({ "R", "S" }, [transsubtype]) && [bdr_hfl] >= 0) ||
            (CONTAINSROW({ "R", "S" }, [transsubtype]) && [bdr_hfl] < 0)
        ),
        BLANK(),
        -[bdr_hfl]
    )
)