Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm experimenting with the Get Data from Power BI datasets functionality in excel. I can connect fine to my power bi dataset and I am then given the ability to create a pivot table. So far so good!
The issue I'm having is that some fields are not available to be selected as values, only as rows or columns. In some cases, the column is formatted as a number in the dataset but when I'm trying to use it in the pivot table I can only use it as a row or column. Not as a value so I can't take the average or sum of the column as an example. In fact, I can't even display it.
An example is a column Sales Value formatted in Power Bi as £,000 but i can only use it as row or a column. This makes me think it is pulling through to excel as text. I can't seem to find a way to change the column in Excel.
It also means I don't have the ability to pull the Sales id column into the values of the pivot table and perform a count to find the number of sales.
I've only just started playing with Excel so maybe there is a secret to this.
If anybody can help..
Thanks in Advance,
O
Solved! Go to Solution.
Yes, only explicit measures will pull through to Excel.
See here for more detail on implicit vs explicit measures:
https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi
On a second look, it appears that the only items I can use as values are measures in the dataset.
Yes, only explicit measures will pull through to Excel.
See here for more detail on implicit vs explicit measures:
https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi
Thanks, that is v helpful. Is there a way in excel to create explicit measures? Or do I have to create the explicit measures in PBI before connecting to the dataset in Excel. It feels a bit messy creating a measure for something which doesn't need a measure in PBI.
It doesn't seem like it.
From these documentation sources:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |