Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

8 REPLIES 8
mastone
Frequent Visitor

I know this is really old, but do you know if the custom visuals support summarization of a hierarchy now?  I have a matrix like dataViewMappings and need to get the summary at each hierarchy level.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors