topic New to Power BI (DAX) in DAX Commands and Tips

New to Power BI (DAX)

Chriswyy — Wed, 26 Jul 2023 02:30:03 GMT

Hi guys, just started to learn power bi.

Would like to understand why error happens when I input the following formula:

=SUM(DISTINCT('Jun23 Data'[INCOME_AMT]))

Error returned: The SUM function only accepts a column reference as an argument.

Assuming below is my dataset

Month : Income_amt

Jan : 100

Feb : 100

March : 200

ps: I know this formula doesn't make sense... to sum distinct values for monthly income. But i just wanna find out how the dax works and why i cant get 300 in total.

Re: New to Power BI (DAX)

Jihwan_Kim — Wed, 26 Jul 2023 03:19:12 GMT

Hi,

If I am not wrong, DISTINCT DAX function returns a table.

However, SUM DAX function needs a column.
Please try something like below.

Or,

Expected result measure: = VAR _t = SUMMARIZE ( Data, Data[Income_amt] ) RETURN SUMX ( _t, Data[Income_amt] )

Expected result measure V2: = VAR _t = DISTINCT(Data[Income_amt]) RETURN SUMX ( _t, Data[Income_amt] )

Re: New to Power BI (DAX)

Chriswyy — Wed, 26 Jul 2023 04:58:59 GMT

hmm.. I thought it will return a column based on the documentation

Re: New to Power BI (DAX)

mlsx4 — Wed, 26 Jul 2023 10:35:57 GMT

Hi Chriswyy

It returns a table with an unique column...That's why you need to use Sumx.
Check this at: https://dax.guide/distinct/#:~:text=DISTINCT%20DAX%20Function%20(Table%20manipulation)&text=Returns%20a%20one%20column%20table,for%20a%20table%20expression%20argument.

Or the official link: https://learn.microsoft.com/en-us/dax/distinct-function-dax