PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Hi,

I have 2 power pivot tabs - Table3 and Table4. Table 3 has 3 columns - Date, Value and ID. Table4 has one column Date. I created a relationship between the date columns of the Table3 and Table4. Now in table4, i wish to create a calculated column to count the distinct ID (from table3) tab for each date mentioned in the date column of table4. When i wrote the following formula, i get the #ERROR value

=countrows(DISTINCT(RELATEDTABLE(Table3[ID])))

Could you help me solve this problem. Thank you.

Ashish,

Sorry for the late reply...

RelatedTable function expects table name as parameter and returns table and function DISTICT expects parameter that is column. That is why you are getting this error message.
To go around this, don't use RelatedTable function, but instead use function Calculate:



Hope this helps

Hi,

Thank you for replying. That gave me the wrong answer. It returned the unique records till the last data in Table4. I got the same answer in all cells. Instead, i need the number of unique ID's for every date mentioned in Table4

Thank you

Hi,

My due apologies, your formula does solve my problem. the answer is right. I had forgotten to establish a relationaship between the date columns of Table3 and Table4. Two more questions here:

1. How can i transfer the outout from power pivot to a worksheet such that the link is maintained

2. Also, i would now like to know unique ID's till a specific date. Your formula below gives the unique ID's for a certain date. How can i change the formula to gice me the answer till a certain date.

Thank you.

Ashish,

There is no special way to transfer output from Powerpivot to another worksheet. You can consider just referencing another sheet like "=SheetName!C58". But if you will change anything in Pivot, cell you are referencing might contain totally different data, so you would have to be very careful.
You might also consider using Cube formulas, where instead of pivot you have each cell converted to formulas. But that would be more advanced topic.

Could you please clarify what do you mean "Till specific date", or more importantly - how would you choose that specific date. Would it be hardcoded in the formula, or would you select it somehow? If so, could you please specify how?

Hi,

Thank you for the reply. I have dates typed in one column (these are in ascending order). In another column i have ID's. On sheet 2, i have a column of dates. In the next column, i just need to know the number of unique ID's till the date specified in previous column. So if there is a date 12 May 2010, then in the next column, i wish to know the unique ID's till 12 May 2010 and not on 12 May 2010.

Any help would be appreciated.

Hi Ashish,

This calcualtion is a bit harder. Here is my description:

You have 2 tables:
Table 3 has fields Date, Value and ID
Table 4 has field Date

You should load both of these tables into PowerPivot and create relationship.
Create Pivot.

Now drop Date field from Table 4 into rows area.
Add new pivot calculation:



Test and see if your results are what you were expecting. In formula above I filter table Table3 to include just records with Date value less than curent value of the Table4 Date for each row.

Hi,

That did not work. I create a relationship between the dates of dummy1 and dummy3. Then i created a pivot table where i dragged data from dummy1 to the row area of a pivot. Dummy1 is the Table4 in your example. Once i create a pivot, where do i have to write the formula?

Not knowing where to write the formula in the pivot created above, i wrote the following calculated formula in dummy1

=IF(CountRows(VALUES(dummy1[Date])), Blank() , CALCULATE(COUNTROWS(DISTINCT(dummy3[ID])), All(dummy1[Date]), dummy3[Date]< VALUES(dummy1[Date])))

This formula gives a value error

Please help