+ Reply to Thread
Results 1 to 7 of 7

Problem with CONCATENATEX measure in pivot table

  1. #1
    Registered User
    Join Date
    02-06-2021
    Location
    Sheffield, England
    MS-Off Ver
    365
    Posts
    2

    Problem with CONCATENATEX measure in pivot table

    Hello. I am a first time poster and new to adding data to the data model when creating a pivot table.

    I need to produce a weekly broadsheet summary of students' lesson attendance marks (which are text characters such as '/' and 'N') and I am limited to exporting the data from my MIS as a list. There are up to 25 rows of data per student (5 lessons per day for Mon-Fri). There are about 1200 students on roll at my school, so the weekly export is c30000 rows.

    I have managed to do this successfully for the past few weeks, but have found that sometimes the 'concatenatex' measure I add to show the actual marks won't go into the 'Sum Values' section of the pivot table. There is no error message and Excel doesn't hang or crash. The 'Reading data' bar appears to progress as normal but then stops just before it completes, before disappearing without the measure being placed in the Values section. Other times it goes in fine. In the attached example I have stripped the initial table down to only 2 students (50 rows) and, as you can see, it works fine.

    I cannot find any information online about what might be causing this and have spent three weeks experimenting to see if I can find any consistent factor when it does not work. I am currently working from home and use a remote desktop which connects me on any given occasion to any of about 30 different PCs at work. My first thought was that this is something to do with insufficient processing capacity on some of them and after several failed attempts on different remote logins, I tried it directly on my own laptop where it worked fine. However, as the weeks have gone on, I can't even get it to work on my own laptop, unless I limit it to just a handful of rows. Could it be RAM or a cache or something? I am using Excel 365.

    I really need to get it fixed and am desperately hoping that someone can shed some light on what is going wrong and how I fix it.

    Many thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Problem with CONCATENATEX measure in pivot table

    What should the expected outcome look like. What is incorrect about what Power Pivot is presenting. You have not told us what is wrong. Only that there is an issue and it is not what you want.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    02-06-2021
    Location
    Sheffield, England
    MS-Off Ver
    365
    Posts
    2

    Re: Problem with CONCATENATEX measure in pivot table

    Thank you for your reply Alan and apologies if I wasn't clear in my original description.

    The expected outcome should like what I have attached. The outcome when it fails is that nothing shows in the 'Sum Values' section. When I am producing one of these reports and I get to the part where I need to drag the 'Actual Mark' measure I have created using the ConcatenateX formula into the the 'Sum Values' section of the pivot table, it doesn't always drop in. It looks like it has but the 'reading data' bar fails to complete before disappearing. The text values do not appear - the cells remain blank as the pivot table has nothing in 'Sum Values' section. No error message appears and Excel doesn't freeze or crash.

  4. #4
    Registered User
    Join Date
    03-17-2021
    Location
    Worcestershire, England
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Problem with CONCATENATEX measure in pivot table

    I'm having this problem too. It's driving me crazy! Did you find a solution?

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Problem with CONCATENATEX measure in pivot table

    @patrickhogan
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  6. #6
    Registered User
    Join Date
    03-17-2021
    Location
    Worcestershire, England
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Problem with CONCATENATEX measure in pivot table

    I don't really understand that response Alan, but thanks for replying anyway . My problem is exactly the same as Jayjay33's problem, so starting a new thread does not seem like the right course of action. I just wanted to find out if Jayjay33 had found a solution.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Problem with CONCATENATEX measure in pivot table

    @patrickhogan Please take a few minutes and read the forum rules you agreed to when you joined.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-01-2020, 02:13 AM
  2. Pivot Table: Grand Total different measure from Column measure
    By chinneywow in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-07-2019, 06:22 AM
  3. [SOLVED] Include only one measure as part of a Pivot Chart/Table
    By tstackhouse10 in forum Excel General
    Replies: 3
    Last Post: 07-01-2019, 06:59 PM
  4. pivot table with DAX measure
    By mlaxx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-12-2019, 02:24 PM
  5. Data Model Pivot Table - Measure Issue
    By CraigsWorld in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2018, 02:54 AM
  6. Pivot Table Calculation (Measure) Formula Help
    By sovietchild in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-21-2018, 10:31 AM
  7. Replies: 1
    Last Post: 06-19-2006, 03:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1