Issue with Bar chart: SummarizeColumns() and AddMissingItems() may not be used in this context

[bibhu250]

Hi All,

I am seeing an issue with a bar chart when I use a calculated measure that includes "Summarizecolumns()" . This measure is working fine with a card visual but when I use this on any other visual, it throws an error "SummarizeColumns() and AddMissingItems() may not be used in this context"

My requirements is as follows, there are 3 tables: DATE, LOCATION, TIMECARD SWIPES(transactional table that contains employee hours worked). I want to see the total count of employees who worked over 40 hours by the location for the selected time period slicer.

The following measure calculates the count of employees who worked over 40 hours. When I select a date slicer and use this measure in a card visual, it works fine but when used in a bar chart with location as axis, it throws error.

Can somebody tell me where am I going wrong? I have attached the workook and screenshot below.

Count_Over40 =

var tablex =

FILTER(

SUMMARIZECOLUMNS(

'Date'[WeekNum_Year],

'Timecard Swipes'[Employee_ID],

'Timecard Swipes'[Location],

"XXHours",SUM('Timecard Swipes'[Work_Hours]))

, [XXHours] > 40)

return

CALCULATE(

COUNTROWS( tablex )

) +0

-----------------------------

[luke6363]

I have a similar issue. I have a list of companies. The companies have products categorized by product code. What I'm trying to do is select a company (or multiple) in a slicer and see the count of products that other companies have in the same product code as the selected company (or multiple). The below DAX works great for that in a simple card, but if I use it in a barchart for example so I can visualize the count by product code, then I get the error SummarizeColumns() and AddMissingItems() may not be used in this context. Please help!

Measure =
CALCULATE(

    DISTINCTCOUNT('TableA'[productid]),

    SUMMARIZECOLUMNS('TableA'[product_code],

    FILTER('TableA','TableA'[company_name] IN VALUES('TableB'[company_name]))))

[bibhu250]

here is the link to download the file.

https://drive.google.com/file/d/1v9zeFsyJ6ovTKpF8OEZ-tzWRquH_5NMa/view?usp=sharing

[mahoneypat · Accepted solution]

It's probably becuase the Location column is in both the visual and the SUMMARIZECOLUMNS().  When SUMMARIZECOLUMNS() throws an error, I sometimes use its less error prone predecessor ADDCOLUMNS(SUMMARIZE().  Here is that version of your measure:

Count_Over40 =
VAR tablex =
FILTER (
ADDCOLUMNS (
SUMMARIZE (
'Timecard Swipes',
'Date'[WeekNum_Year],
'Timecard Swipes'[Employee_ID],
'Timecard Swipes'[Location]
),
"XXHours", CALCULATE ( SUM ( 'Timecard Swipes'[Work_Hours] ) )
),
[XXHours] > 40
)
RETURN
CALCULATE ( COUNTROWS ( tablex ) ) + 0

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[bibhu250]

yes, this works.

Thanks much.