Can a Measure create more than one value?

Maggie Barr

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

Maggie Barr

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

Board Regular
Joined
Apr 30, 2014
Messages
247
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?
 

Forum statistics

Threads
1,078,148
Messages
5,338,521
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top