DAX Error A single value for column 'Master Company Sum' in table 'Deep Dive' cannot be determined.

[Anonymous]

I keep getting the error:

A single value for column 'Master Company Sum' in table 'Deep Dive' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Here is what I have:

Diff. Between CY and LY = IF( ISBLANK( [Product Value LY] ), BLANK(), ('Deep Dive'[Master Company Sum]) - [Product Value LY])

I should mention that I did the "Group By" function in the initial Query so I am suspecting that this may be confusing things, but not sure why. The formula worked before when I did not have the "ISBLANK" language. I just need previous year's product values to not show in results if they are "0"

[v-gizhi-msft]

Hi,

According to your description, i create a sample to test:

Then i create these two measures:

Product Value LY = CALCULATE(SUM('Deep Dive'[Product Value]),FILTER(ALLSELECTED('Deep Dive'),'Deep Dive'[Company] in FILTERS('Deep Dive'[Company])&&'Deep Dive'[Date]=DATE(YEAR(MAX('Deep Dive'[Date]))-1,MONTH(MAX('Deep Dive'[Date])),DAY(MAX('Deep Dive'[Date])))))

Master Company Sum = SUMX(FILTER(ALLSELECTED('Deep Dive'),'Deep Dive'[Company] in FILTERS('Deep Dive'[Company])),'Deep Dive'[Product Value])

Then try the measure as the same with you:

Diff. Between CY and LY = IF( ISBLANK( [Product Value LY] ), BLANK(), ('Deep Dive'[Master Company Sum]) - [Product Value LY])

The result shows well:

So for your issue, please check my process and my pbix file here:

pbix 

Also, could you please share Group By function details you used in Query Editor as a screenshot?

Expect your reply!

Best Regards,

Giotto Zhi

[Anonymous]

Thank you for your help. I altered the language based on my fields:

Product Value LY = CALCULATE(SUM('Deep Dive'[Master Company Sum]),FILTER(ALLSELECTED('Deep Dive'),'Deep Dive'[Master Company]) in FILTERS('Deep Dive'[Master Company])&&'Deep Dive'[Process Date]=DATE(YEAR(MAX('Calendar Table'[Date]))-1,MONTH(MAX('Calendar Table'[Date])),DAY(MAX('Calendar Table'[Date]))))

Got the error message: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I did not touch the Master Company Sum because there is no DAX language present to begin with. This is in fact the row that I did the "Group By" function in Query Editor - I had to group several sales in one year by company so that I had a company's sales for the year rolled up into one total. (Master Company Sum is a title, not an actual function I did within DAX). I cannot share picture of my query editor for confidentiality reasons. 

You've been a great help already, any feedback to the above error would be great!

[v-gizhi-msft · Accepted solution]

Hi,

Please change the formula to this:

Product Value LY =
CALCULATE (
    SUM ( 'Deep Dive'[Master Company Sum] ),
    FILTER (
        ALLSELECTED ( 'Deep Dive' ),
        'Deep Dive'[Master Company] IN FILTERS ( 'Deep Dive'[Master Company] )
            && 'Deep Dive'[Process Date]
                = DATE ( YEAR ( MAX ( 'Calendar Table'[Date] ) ) - 1, MONTH ( MAX ( 'Calendar Table'[Date] ) ), DAY ( MAX ( 'Calendar Table'[Date] ) ) )
    )
)

If you still have issue, please for free to let me know.

If you can share relationships among your tables as a more clear screenshot, it will be better.

Expect your reply!

Best Regards,

Giotto Zhi

[Syndicate_Admin]

Hello is giving me something similar. I need a dax that allows me to add an index to this:

evaluate
GROUPBY(
(
FILTER(
FILTER(
FILTER(
FILTER(
ServicePointTelemetry,RELATED(ServicePoint[ServicePointId]) = 5),
ServicePointTelemetry[metric_type_id] = 129),
ServicePointTelemetry[UTC] >= DATE("2022", "01", "01") + TIME("00", "00", "00")),
ServicePointTelemetry[UTC] <= DATE("2022", "02", "04") + TIME("00", "00", "00"))),

'Time'[Minute],
'Date'[TheDate],
"RESULT",AVERAGEX(CURRENTGROUP(),[metric_value]))

order by 'Date'[TheDate],'Time'[Minute],'Time'[Minute] DESC

What I have:

Time[Minute]	Date[TheDate]	[RESULT]
0	02/02/2022 0:00	12,3016667
1	02/02/2022 0:00	12,3483333
2	02/02/2022 0:00	12,153
3	02/02/2022 0:00	12,242
4	02/02/2022 0:00	12,274
5	02/02/2022 0:00	12,3346667
6	02/02/2022 0:00	12,1996

What I want:

Time[Minute]	Date[TheDate]	[RESULT]	Index
0	02/02/2022 0:00	12,3016667	1
1	02/02/2022 0:00	12,3483333	2
2	02/02/2022 0:00	12,153	3
3	02/02/2022 0:00	12,242	4
4	02/02/2022 0:00	12,274	5
5	02/02/2022 0:00	12,3346667	6
6	02/02/2022 0:00	12,1996	7