DAX Measure to count text items - must be a more elegant way

[rodwhiteley]

Sorry, DAX newbie here.

I have a biggish dataset (about 4 million rows) and in one column ( [Type] ) there are 42 unique text items ("Item 1","Item 2", ... "Item 42") that I'd like to summarise in a Pivot by date.

My clumsy solution as proof of concept was to create a series of 42 calculated columns in this table along the lines of:

IsItem1
=if([Type]="Item 1",1,0)

Then the next calculated column is
IsItem2
=if([Type]="Item 2",1,0)

etc.

I am then able to Pivot the sums of these columns against the dates, as well as summing them to make compound items (eg "IsItem1or2:=sum([IsItem1],[Item2]) but this seems clumsy and inelegant.
More importantly likely a waste of memory and is probably slowing down performance.

What is the better approach here, or should I just write the 42 extra columns?

Thanks in advance for any help

 

[Matt Allington]

Welcome to the exciting world of Power Pivot. Indeed there are much better ways. Calculated Columns are what most novice Power Pivot users (that come from Excel) try to use, but it is normally the wrong approach. Power Pivot is very different to Excel, but you can learn it. I suggest you buy a good book - this will lay the foundations for you and get you off to a good start. As it turns out I have written a very good book for people just like you. Read the reviews on Amazon and then decided if it is right for you. Learn to Write DAX - the book for all Excel users

To answer your questions
Don't create any columns.
Insert a new pivot table
put your column [Type] on Rows in the pivot
Write the following Measure (Calculated Field in Excel 2013) and add it to values.
Count = countrows(myTable)

 

[anvg]

Hi
If I understand you right, you want to have a measure which calculates count items for "Item 1", "Item 2" only. For that you can use CALCULATE function.
Let your table name is myTable

IsItem1or2:=CALCULATE(COUNTROWS('myTable'), 'myTable'[Type]="Item 1" || 'myTable'[Type]="Item 2")

Regards,

 

[rodwhiteley]

Not quite, I need to know the numbers of each individual item (there are 42 unique values), but I think your solution will work by me writing 42 measures (IsItem1; IsItem2; IsItem3:...) and then any combinatorial measures as you suggest.
Thanks for the input, I'll check and see if this approach works.
Rod

 

[Matt Allington]

You don't need to write 42 measures. Just follow the instructions I provided above - only 1 measure required to deliver the total of each of the 42 items in your list.

 

[rodwhiteley]

You don't need to write 42 measures. Just follow the instructions I provided above - only 1 measure required to deliver the total of each of the 42 items in your list.

Thanks Matt, that makes sense, looking up your book now.
If I need to make combinatorial sums (eg what's the total of, say, items 1,3,4, and 8) I'd then write individual measures there?
It seems OR can only handle 2 arguments. What's the best approach to summing such arbitrary individual amounts? (I guess a slicer might work...)

 

[Matt Allington]

If you need these combinations, you definitely can write a hard coded measure for this. But if these are common groupings, it is easier/better to create a lookup table (you actually should create a lookup table anyway).

So you could have a lookup table like this.

Type      Group
Item 1    A
Item 2    B
Item 3    A
Item 4    C
Item 5    B
etc

Then instead of putting the Type in your pivot table, you put the Group instead. Using this approach makes it much easier to maintain the groupings and you can reuse the one single measure.

There is no one measure, it depends on your needs.