cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIcustomizer
Frequent Visitor

DataViewMappings for hierarchical data with partial filtering

I have two groupings (category and subcategory), which I would like to represent in a hierarchical way in a custom visual.

Additionally to the measures filtered on the combinations of the two groupings, I would like to have access to the measure value for each value of the first grouping (the category) without the split into the additional subcategory.

 

The matrix visual does this and shows what I want under "Total". How can I configure a DataViewMapping to give me this information? Note that simply summing is not sufficient, as subcategories need not be unique or the measure could be a some ratio that doesn't have to sum up to 1.

7 REPLIES 7
v-viig
Community Champion
Community Champion

Hello @PBIcustomizer

 

Totals and subtotals ara not supported by Power BI Custom Visuals API yet.

They are in backlog but there's no ETA.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Hello,

 

Thanks for your reply.

Another option would be to use two category fields, one just for "Category" and a second one with "Category" and "Subcategory" (configured as groupings). Can I configure a DataViewMapping that works like this? it would be easy to do with two mappings, but as far as I know this is not possible.

Any other way out?

 

Yes're correct. Power BI Custom Visuals API doesn't support multiple data-views at lest for now.

I suppose that you might use the table mapping for your case.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

How exactly could I do that?

Currently I have this:

 

{
    "dataRoles": [
        {
            "displayName": "Category 1",
            "name": "category",
            "kind": "Grouping"
        },
        {
            "displayName": "Category 1 & 2",
            "name": "category2",
            "kind": "Grouping"
        },
        {
            "displayName": "Measure Data",
            "name": "measure",
            "kind": "Measure"
        }
    ],
    "dataViewMappings": [
        {
            "conditions" : [
                {
                    "category": { "max": 1 },
                    "category2": { "max": 2 }
                }
            ],
            "table": {
                "rows": {
                    "select": [
                        { "bind": { "to": "measure" } },
                        { "for": { "in": "category" } },
                        { "for": { "in": "category2" } }
                    ]
                }
            }
        }
    ]
}

Basically, I want the measures filtered by 'category' AND (completely separately) filtered by everything in 'category2'. That's not what this mapping does - how can I change it to get what I need?

 

For example: if Category and Subcategory are two columns in my data, I would be to put Category in the 'category' field and Category AND Subcategory in the 'category2' field. The filtering by 'category' would give the aggregate values per Category and filtering by 'category2' would give the values one level further down in the hierarchy, for each combination of Category and Subcategory.

As far as I understand you want to implement something like Drill down.

Is my understanding correct?

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

In principle yes, but I want access to the different hierarchies all at once.

I tried using drill down, but then I only get one specific view- either the top level (only one category) or the data filtered on everything.

Is there a way to combine the hierarchies when using drill down? Maybe persisting the data from the higher levels?

As far as I know, Power BI doesn't support combining hierarchies.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors