PowerPivot DAX Conditional IF automation or reference

[MikeGkp]

Hi, I would like to create a column in the powerpivot data table that will change upon updating a master date or criteria.

I have a functioning If statement that works well: =IF([year]=2010,[count],0)

Which provides the result that I need when I hard code the number 2010 into the if statement as shown above. However, I want to be able to change the '2010' portion of the if statement by changing a reference field in a table or excel tab within the workbook. The variable that will replace '2010' could be a year, month, date, or other criteria.

Any suggestions?

Thank you

Mike

 

[Matt Allington]

You are going about this the wrong way (a very common approach for Excel users). You need a measure, not a column.
Make sure you have a calendar table
Assuming [count] is a measure (and not a column) then Create another measure
=calculate([count], allselected(calendar[year]))
put the measure in a pivot table and add slicers for year, month etc

If count is a column, then you need a formula instead, depending on your data.
Eg one of these instead

=calculate(sum(myTable[count]), allselected(calendar[year]))
=calculate(count([count]), allselected(calendar[year]))

my book is specifically designed to help Excel users get started and I am sure it will help you
Learn to Write DAX - the book for all Excel users

 

[MikeGkp]

Thank you Matt, I should receive your book today from Amazon. Does your book cover the best way to pass the slicer selection back to the calculated measure so that the pivot table reflects the summation of the count? Count is a column with numeric values. Mike

 

[Matt Allington]

I'm not 100% sure if I understand the question. But my book definitely covers initial filter context in detail. Initial filter context is the natural filtering created by the pivot table and the slicers. Here is a small extract

*****
Pivot tables, by their very nature, "filter" data. The filtering applied to a pivot table is called the initial filter
context—initial because it is possible to change the filter context coming from a pivot table by using the
CALCULATE() function. (This is covered in Chapter 8.) So the initial filter context is the standard filtering
coming from a pivot table before any possible modifications from DAX formulas using CALCULATE().
The initial filter context comes from four areas of a pivot table:
• Rows
• Columns
• Filters
• Slicers
****

Once you understand what the pivot table does, you then learn how to use CALCULATE to modify this natural behaviour.

If you don't get the answers you need from the book, ask me a question at powerpivotforum.com.au (as recommended in the book) and I will help you.

If you like the book, I would appreciate a review on Amazon.