Forum Discussion
Get all columns in custom visual
I'm trying to build a custom pivot table visual for PowerBI, and to do that I'm using the "matrix" dataview mapping, like this:
"dataRoles": [
{
"name": "Category",
"displayName": "Category",
"displayNameKey": "Visual_Category",
"kind": "Grouping"
},
{
"name": "Column",
"displayName": "Column",
"displayNameKey": "Visual_Column",
"kind": "Grouping"
},
{
"name": "Measure",
"displayName": "Measure",
"displayNameKey": "Visual_Values",
"kind": "Measure"
}
],
"dataViewMappings": [
{
"matrix": {
"rows": {
"for": {
"in": "Category"
}
},
"columns": {
"for": {
"in": "Column"
}
},
"values": {
"select": [
{
"for": {
"in": "Measure"
}
}
],
"dataReductionAlgorithm": {
"window": {
"count": 30000
}
}
}
}
}
]
However, I'm having trouble with the API not returning all the data.
In particular, if I add a lot of columns, a warning appears on the visual saying that there are "too many values" and that not all of them can be displayed.
I've read about the "dataReductionAlgorithm" property, but setting it on the "columns" prevents the visual from loading at all.
I've also read about the "fetchMoreData" api, but invoking it doesn't seem to help at all with the problem.
So, is there a way to get all the columns in a matrix? Or am I just s**t out of luck and I have to get data in simple table format and pivot it myself in code?
8 Replies
- v-lionel-msftCommunity Support
Hi Master_T ,
Please refer to the link.
Understand data view mapping in Power BI visuals
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Master_THelper I
Sorry, but linking me the basic documentation on data view mappings doesn't really answer my question...
As I showed in the OP, the mapping configuration is correct, what I'm asking is how to retrieve ALL the data in the matrix when it exceeds the limit for a single data fetch, since the "fetchMoreData" API, from what I've discovered by testing, seems to only work in "table" mappings, it does nothing when called in "matrix" mappings.
- jprdynamicappsHelper IV
I'm working on the same issue with my custom matrix visual. I get 10 columns back even though there are more values in the column list. In previous PBI versions, I didn't get any warning, but in the current version, I get the "Too many [column] values" message.
FYI, the "Simple Matrix" visual at https://github.com/microsoft/Powerbi-Visuals-SampleMatrix does not exhibit this problem, so I'll be looking at the differences between mine and that re data mapping configuration and the traversal/fetch more data code that I have. Will post back if I solve it.
- Master_THelper I
That is interesting, have you discovered anything further? I will keep this in mind in the future if I need to use pivoted data again.
- jprdynamicappsHelper IV
Sorry, it ended up elsewhere in the thread instead of as a reply to this message.
I was able to resolve the problem by adding the same data reduction algorithm setting to columns. No clue why I have to set that in order to get more than 10 columns, but it worked. I believe the default for rows is 1000. Maybe the default for columns is 10?
- jprdynamicappsHelper IV
Well, I thought I tried this a year ago, but I was able to resolve the problem by adding the same data reduction algorithm setting to columns. No clue why I have to set that in order to get more than 10 columns, but it worked. I believe the default for rows is 1000. Maybe the default for columns is 10?
Hope that helps someone.