Transform Excel Command to DAX or M Language

[tksnota]

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,

[_AAndrade]

Hi @tksnota,

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

[tksnota]

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]

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

[tksnota]

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

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

[_AAndrade]

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

[tksnota]

Hi,

I have these errors.

Thanks...tksnota...

[_AAndrade]

Hi @tksnota,

Could you share a pbix file with same data so I could take a look?

[tksnota]

Hi,

How about we change the Dax wherein any GL Code that starts with "7" and "4" will be extracted to Calculated Column called Debit and anything that starts with "8" will be credit (as shown)?

[_AAndrade]

@tksnota,

I'm attaching a pbix file with a quick example. See if this could help you.

[tksnota]

Hi 

Thanks for the pbix.

I tried your DAX but my problem is the system won't show the column only the sum as shown from the black box.

Anything I need to do so I can get the correct columns?

Thanks...tksnota...

[_AAndrade]

Hi,

What column you're using on your Sum measure? Should be that column, I guess...
I can help you more than this with the information than I have... sorry

[tksnota]

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

[_AAndrade]

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

[Kedar_Pande]

@tksnota 

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