Get all columns in custom visual

[Master_T]

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? 

[jprdynamicapps]

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.

[jprdynamicapps]

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_T]

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.

[jprdynamicapps]

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?

[v-lionel-msft]

Hi @Master_T ,

Please refer to the link.

Understand data view mapping in Power BI visuals 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Master_T]

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.

[Eugene_Ov]

@Master_T  not sure it would be useful to you, but I'd suggest to investigate default matrix behavior  and compare it to your custom visual. You can check what queries are sent to the storage engine and find patterns.

Eugene.

[Master_T]

I don't know if the vague/evasive answers in this thread are because I didn't explain myself well (sorry in that case) or what, but I searched far and wide and I didn't find a way to overcome this limitation of the API. A limitation that is quite bizzarre to me, why arbitrarily limit the number of columns I can fetch in a professional BI product? I wonder if PoweBI Premium also has this limitation and this is a subtle way to "nudge" users into upgrading...

At any rate, in the end I had to use a normal table, instead of a matrix, I then had to write code to group the fields and compute the data aggregates myself, basically rebuilding the matrix in code from the linear table data... which again, is a ridiculous thing to do in a professional BI product, but I couldn't find any way around it.