Forum Discussion

Master_T's avatar
Master_T
Helper I
5 years ago

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

    • Master_T's avatar
      Master_T
      Helper 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.

      • Eugene_Ov's avatar
        Eugene_Ov
        Helper I

        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.

  • 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's avatar
      Master_T
      Helper 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.

      • jprdynamicapps's avatar
        jprdynamicapps
        Helper 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?

         

  • 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.