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

    can i get it for calculated table...thanks...

    • _AAndrade's avatar
      _AAndrade
      Resident Rockstar
      1 year ago

      I don't know if I understood well your question, but try this:

      CalculatedTable = 
ADDCOLUMNS(
    'OriginalTable', // The name of your table
    "@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...