Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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%....
Or the official link: https://learn.microsoft.com/en-us/dax/distinct-function-dax
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] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
hmm.. I thought it will return a column based on the documentation
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%....
Or the official link: https://learn.microsoft.com/en-us/dax/distinct-function-dax
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |