Getpivotdata equivalent

[luciefialova]

Hi,

Is there a Power BI equivalent to the getpivotdata function in Excel? Some teams within my organisation have quite messy reporting requirements (asking me to count apples and oranges) and in the past I would have done something like getpivotdata(A) + getpivotdata(B) to get the figure they need.

Apologies if there's a really obvious way of doing this that I've missed, I'm still fairly new to Power BI.

Thanks

Lucie

[v-ljerr-msft]

Hi @luciefialova,

After a few research on the getpivotdata function in Excel, I don't think there is an exact equivalent function in Power BI. However, I believe it could be implemented with other DAX functions(i.e. SUMMARIZE function). So could you post your table structures with some sample data and your expected result, so that we can further assist on this issue?

Regards

[luciefialova]

Hi,

Thanks for that. Maybe the problem is that I always have my SQL hat on and that's not how power BI works... I've made these two matrices, they are looking at count of people who had a tag added to their record and a count of people who made payments coded to a certain code. What the client wants to see is a sum of the two totals (130+18 and so on). In excel, I would have created these pivot tables on a hidden sheet and used getpivotdata to sum the two values and displayed just the sum figure. What I've done isn't very elegant (wait till you see my table structure 😄 I'm still working this out!!)

The attributes table is behind the first matrix and the transactionCF.... table behind the second one (that query finds people who made transaction AND don't have any of those attributes).

What's in the two tables:

Thanks

Lucie

[v-ljerr-msft · Accepted solution]

Hi @luciefialova,

I would suggest you start to learn DAX language which could calculate all the expected result for you in Power BI without return back to SQL. And it's better to read a book from beginning. 

In addition, the formula(DAX) below to calculate the sum of the two totals mentioned above is for your reference.

Measure =
SUMX (
    SUMMARIZE (
        'Attributes',
        'Attributes'[ATTRUBUTE_TYPE],
        "count of people", COUNTROWS ( Attributes )
    ),
    [count of people]
)
    + SUMX (
        SUMMARIZE (
            'transactionCF',
            'transactionCF'[CAMPAIGNCODE],
            "count of people", COUNTROWS ( transactionCF )
        ),
        [count of people]
    )

Regards

[luciefialova]

They'll then want to see even more complicated indicators such as the count of people who had attribute A added in a time period oct/16-now against the count of people who had attribute B added in this financial year who had attribute A added in that previous time period. I used to get the numbers for them just running SQL queries every quarter but got bored of that and now I'm trying to create something in Power BI that I don't have to touch again.