Results 1 to 5 of 5

Thread: Can a Measure create more than one value?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can a Measure create more than one value?

    Hello and thank you in advance if you can help,
    I am VERY new to using measures. I am using Excel 2019 on a PC.

    I have created some measures that will give me a distinct count of species in a query/dataset, as well as a distinct count of blocks. My data has fields/columns for All Regions, Region, BlockType, Block Name, and Species Name.

    I have been searching for a way to see if I can create measures based on groups within the same table. For instance, I need a measure of the number of Block Names each species occurs in grouped by the Region and Block type so that when I create my pivot table I can put that in a column as it relates to each species in the row.

    I have been able to do this using a number of reference queries by grouping and counting and summing numbers as needed, but the number of queries I created is almost mind boggling, and it is making my workbook run very slow as it progresses, so I am trying to find a way to do some of it with measures.

    Can a measure store information as a series of numerical values for each group within a larger dataset, or do you have to create a query that groups the information first?
    Thank you, any advice is appreciated,
    Maggie
    Last edited by Maggie Barr; Apr 19th, 2019 at 12:03 PM.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,212
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can a Measure create more than one value?

    Hello,

    Seems that you Pivot Table needs some grouping and a few calculated fields ...

    see : https://www.contextures.com/excel-pi...ted-field.html

    Hope this will help

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a Measure create more than one value?

    James006,
    Thank you for the input, but I had looked into that before, and the calculated fields section is always grayed out. I think it is because I am working off of linked tables, so that feature is not available to me in my pivot table. I really don't want to have to load all my queries to tables in the workbook. If you think I am wrong and there should still be a way for me to utilize that, please let me know. I am still playing with the measures and making progress, but grouping be two columns and calculating a DISTINCTCOUNT function within the measure hadn't been resolved yet.
    Thank you,
    Maggie

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,212
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can a Measure create more than one value?

    Hello,

    To count Unique items ...

    https://www.contextures.com/pivottablecountunique.html

    Hope this can help

  5. #5
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a Measure create more than one value?

    Maggie -

    When you say you have created measures, can you post the DAX? You're title for this thread is significant - a measure creates a single value kinda sorta, but depending on the filter context it creates a value for each row or column in the filter context. You can perform a calculation such as SUM or Count for categories within a group. I realize this is greek to you right now, but can you give specific examples?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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