By Kasper de Jonge, crosspost from PowerPivotblog.nl

A while ago i had a question on my ask a question page, Sasha wanted to do the following:

How to define the date I want to see as “open items”.

E.g. Open Items by 25.05.2010
Item 1: Posting Date: 20.05.2010 Clearingdate: 28.05.2010 Value 100 EUR
Item 2: Posting Date: 22.05.2010 Clearingdate: 27.05.2010 Value 200 EUR
Item 3: Posting Date: 23.05.2010 Clearingdate: 24.05.2010 Value 300 EUR

Result will be 300 EUR (Item 1 + 2) Postingdate 25.05.2010 OR Clearingdate = 00.00.0000).

But how to select the 25.05.2010 for this calculation. I have not a date like 25.05.2010 in my Pivottable?

We have two challenges here:

  1. Create a between like measure using dax
  2. Get values from a slicer to be used inside this calculation, the data from this slicer mustn’t effect the data inside the pivottable

I have created the following simplified scenario to recreate sasha’s question. I started with a dataset:

I want to get the sum of amount where my slicer is between values a and b. The first thing i want to solve is how to get a value from a slicer inside my calculation.

To do this i created a new dataset with the values i wanted to use in my slicer, since i might want to use a value that is not inside my powerpivot data. I loaded this into PowerPivot:

This made sure of two things: i have the data i want inside a slicer and because i didn’t create a relationship between this data and the fact table nothing will happen when i select data from the slicer.

Because the data from the slicer isn’t connected to my main fact table doesn’t mean we cannot get data from it. The slicer will make sure the table i just loaded will be “sliced” to the value we want.

When we create the pivottable we seen the following:

Because we didn’t create a relationship PowerPivot keeps reminding us a relationship might be needed, because this is pretty annoying we can turn this off by clicking on the Detection button:

So we are good to go, to get the value of the slicer we can now do sum(aantal[aantal]) in our measure. A measure that uses the value from the slicer would be:

Slicer measure=if(COUNTROWS(aantal) = 1, sum(aantal[aantal]), BLANK())

This will check if we have only one value selected, and if that is the case return the sum of the column. This results in the single value because the table has one row (it is sliced to one row). Otherwise return BLANK(). This will look like:

We now have the value of the slicer in our pivottable and we can use it to create a measure that will give us the sum of amount where the value of our slicer is between a and b.

What we are going to do use sumx to sum Table1[amount] over a filtered table, this table will filter the values where the value of slicer aantal between column a and b. This will look like:

=IF(COUNTROWS(aantal) = 1 ,
			SUMX(FILTER(Table1,
				Table1[a] <= sum(aantal[aantal])
				&& sum(aantal[aantal]) <= Table1[b]
			)
			,Table1[amount])
	, BLANK())

step by step:

  • Check if we have selected only one value in our slicer, if more return blank
  • Do a sum of Table1[amount] over a filtered table, more on sumx at this blog post of Rob Collie.
  • Use filter to return a dataset of specific values from a table, in this case return all rows where value of column a <= value of the slicer and value of the slicer <= value of column b

This will look like this in our Pivottable, as we can see we only have the rows where our slicer measure is between a and b:

When we remove a and b from the pivottable we see the total sum:

This again shows you the amazing power of DAX, a lot is possible. But a word of caution is at his place, SUMX and FILTER are two of the most CPU consuming functions in PowerPivot, it creates a new dynamic in memory table for every cell in the pivot where FILTER is used. I tried a similar function at a Pivottable based on Contoso and it took me a lot of CPU. Check out this blog post of Rob Collie on the use of filter. SUMX and FILTER are amazing giving you all kinds of possibility’s but you need to think where to use what as you can read in Rob’s blog post.

  Subscribe to PowerPivotPro!
X

Subscribe

This Post Has 14 Comments

  1. If columns ‘a’ and ‘b’ were dates and the aantal table contained dates, how would you refer to the slicer values? I have tried to change the above to reflect this but I get a context error message?

    Thanks
    Garry

    1. Whenever you get the “Could not be determined in the current context” error, you usually have referred to a column without “wrapping” it in an aggregation function.

      If you have a date slicer and want to fetch its selected value, try FIRSTDATE(Table[Column]) or LASTDATE(Table[Column]).

      If you have a text slicer, you need to do IF(COUNTROWS(VALUES(Table[Column]))=1, VALUES(Table[Column]), BLANK()) or similar.

  2. Hi,

    I have a similar kind of issue with dates and changed everything from sum to FIRSTDATE except sumx. I am able to create the measure but upon using that measure it gives me the below error.
    “ERROR – CALCULATION ABORTED: Calculation error in measure ‘FactAnnualized'[Measure 1]: An invalid numeric representation of a date value was encountered”.
    Please help me with this.
    Thanks
    Tinku

    1. Check your date columns in the PowerPivot window and verify that they are in fact dates rather than strings?

      Otherwise, please share the formula of the measure that is failing and I’ll take a look.

  3. Hi,

    Thanks for the quick reply. Please see below for the formula. Please let me know.

    IF(COUNTROWS ( FIRSTDATE( ‘DimTime'[Pk_Date] ) ) = 1 ,
    SUMX( FILTER(Fact,
    FIRSTDATE(Fact[ExecDate] ) <= FIRSTDATE( DimTime[Pk_Date] )
    && FIRSTDATE( DimTime[Pk_Date] ) <= FIRSTDATE(Fact[STOPBILLDATE] )
    )
    ,Fact[SuitPrice] )
    , BLANK() )

    Tinku

  4. =SUMMARIZE(salesdetails,[ItemNames],salesdetails[Type of item],”Calc”,SUM([Number of items]))
    i want to sum number of items based on group on ItemsNames column, so i am using the above expression for applying group. Syntax is correct but getting error as “The expression refers to multiple columns. Multiple columns cannot convert to scalar value” .May i know the reason, may i did mistake in any ware

  5. I know it’s not necessarily on this issue, but I’m having a problem with some slicers. I have 2 tables in my data model connected through a relationship. The relationship works, because I can get data into one table from the other one using RELATED. The problem is that, when I’m adding in the Excel sheet a pivot table which gets its data from the first table, and I use a slicer based on a column in the second table, the slicer doesn’t work on the data from the first table. Any ideas?

  6. I use your tips a lot and we just ordered your book. (and Kasper’s as well.). Great information.

    I’m having issues getting the disconnected slicer to work.

    I have a simple “Table1” that lists 5 display units of measure. They correspond to 5 columns in the fact table. A calculated measure will compute the QTY x UOM for the units selected on the slicer.

    I added the units of measure Table1 to the data model and created a slicer on my pivot table tab.
    No matter what I select on the slicer, I always get all 5 values in the measure.

    DisplayUnitSelected:=
    IF( HASONEVALUE( Table1[DisplayUnits] ) , VALUES( Table1[DisplayUnits]) , “Ltr”)

    The result is that my SWITCH function always returns the default value “Ltr” and never the one selected on the slicer.

    SelectedUnitShipped:=SWITCH( [DisplayUnitSelected] ,
    “BBLs” , [Sum of QtyBBLsShipped] ,
    “Gals” , [Sum of QtyGalsShipped] ,
    “CEs” , [Sum of QtyCEsShipped] ,
    “Ltr” , [Sum of QtyLtrsShipped] ,
    “hL” , [Sum of QtyhLShipped] , [Sum of Qty_Document] )

    I found several examples on line and they are all similar to what I did. What am I missing?

    1. I figured it out. I was adding the slicer from the disconnected data table. When I added the slicer field to the pivot table and then added the slicer there it worked.

      1. Hi James, can you please explain it in a bit more detail “When I added the slicer field to the pivot table and then added the slicer there it worked.”……..i am just a newbie getting excited with PP because of Rob and this website

        1. Here was how I got it to work.
          My disconnected table has one field: Unit of measure.
          1. Add the Unit of measure field to the pivot table temporarily.
          2. Select/activate the pivot table and choose insert slicer from the analyze tab.
          3. Add a slicer for the Unit of Measure field to the pivot table
          4. Once I had the “disconnected slicer” on the page I removed the Units field from the pivot table.
          …Now the slicer selection filtered through the measure and displayed the totals in the correct unit.

          I’m not sure if others have this issue or go through this same process, but this was the only way I could get it to work. Every other thing I tried resulted in no slicer selection when I used the Units field in a measure. (see formula for “DisplayUnitsSelected” in the original post.)

  7. I am using Excel 2013 and PowerPivot 2013 for Excel– part of the Professional Plus 2013 suite. I have a 32 bit machine with 2.88 Gb usable ram. I am unable to see, access, or in any way use the PowerPivot Field list for my pivot tables. I can only see the PivotTable Field list used in normal Excel pivot tables. Even when I click on the PowerPivot ribbon, click Manage, upload a table to my data model, then create a pivot table from PowerPivot by clicking on PivotTable, I still only see PivotTable Fields, not PowerPivot Fields. How can I access the PowerPivot Field list? I tried recreating the example above and realized I couldn’t access the PowerPivot Field list.

  8. Hi! I found this blog today when looking for help with a similar situation I have like the above. I tried it out and it work great, but I have an existing power pivot with several tables and slicers that all have relationships. My user wants to add a slicer that will be used only if clicked. So yes, I created another table with the data to display in the new slicer. I did not set up a relationship. And yes I can get the slicer measure to work and the measure to bring back only the rows that match. BUT it does not affect my original pivot table results. How do only include this slicer when clicked to affect all the other slicers that are used to control what data is returned? Thank you

Leave a Comment or Question