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')
AND gbkmut.bdr_hfl >= 0) )
OR (gbkmut.transsubtype IN ('R','S')
AND gbkmut.bdr_hfl < 0))
THEN gbkmut.bdr_hfl ELSE NULL END) AS Debit,

 

(CASE WHEN transtype IN('N','C','P','F')
AND (((gbkmut.transsubtype NOT IN ('R','S')
AND gbkmut.bdr_hfl >= 0) )
OR (gbkmut.transsubtype IN ('R','S')
AND gbkmut.bdr_hfl < 0))
THEN NULL ELSE -gbkmut.bdr_hfl END) AS Credit,

Need Help

14 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Anonymous 

    Debit = 
    SWITCH(
    TRUE(),
    'gbkmut'[transtype] IN {"N", "C", "P", "F"} &&
    (
    ('gbkmut'[transsubtype] NOT IN {"R", "S"} && 'gbkmut'[bdr_hfl] >= 0) ||
    ('gbkmut'[transsubtype] IN {"R", "S"} && 'gbkmut'[bdr_hfl] < 0)
    ),
    'gbkmut'[bdr_hfl],
    BLANK() // Equivalent to NULL
    )
    Credit = 
    SWITCH(
    TRUE(),
    'gbkmut'[transtype] IN {"N", "C", "P", "F"} &&
    (
    ('gbkmut'[transsubtype] NOT IN {"R", "S"} && 'gbkmut'[bdr_hfl] >= 0) ||
    ('gbkmut'[transsubtype] IN {"R", "S"} && 'gbkmut'[bdr_hfl] < 0)
    ),
    BLANK(), // Equivalent to NULL
    -'gbkmut'[bdr_hfl]
    )

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • _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.