Can a Measure create more than one value?

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
268
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,096,357
Messages
5,449,961
Members
405,580
Latest member
MRJT

This Week's Hot Topics

Top