Multiple dataViewMappings

[cchilton]

I'm building a custom KPI table visual that includes a sparkline. To render the sparkline, I need the raw (unaggregated) data by date. However, I also need aggregated totals to display in a separate "Totals" column.

Right now, I'm using a categorical dataViewMapping grouped by date, which gives me each individual value. But I also need the total (e.g., sum or a custom DAX measure) across all dates.

I tried something like this in capabilities.json:

"dataViewMappings": [

  {

      "categorical": {

          "categories":

              { "for": { "in": "metric" } }

          ,

          "values": {

              "group": {

                  "by": "date",

                  "select": [

                      { "for": { "in": "total" } },

                      { "for": { "in": "target" } },

                      { "for": { "in": "kpi" } },

                      { "for": { "in": "format" } },

                      { "for": { "in": "tooltips" } }

                  ]

              }

          }

      }

  },

{
"table": {
"rows": {
"select": [
{ "for": { "in": "metric" } },
{ "for": { "in": "total" } }
]
}
}
}

]

I know Power BI only activates one mapping at a time, so dataView.table is null when categorical is active. Is there any way to get both raw and aggregated values into the same dataView?

[cchilton · Accepted solution]

This work around worked for me. This approach makes the aggregatedTotals measure repeat the same total for each date row — so you can show both the sparkline and the overall KPI side-by-side.

1. Create a DAX measure for the aggregate over a fixed date range (I used variables instead of hardcoded dates):

TotalBetweenDates =
CALCULATE(
[Total],
FILTER(
ALL('Date'),
'Date'[Date] >= DATE(2025,1,1) && 'Date'[Date] <= DATE(2025,3,31)
)
)

2. Add the new data role in capabilities.json:

{
"displayName": "Aggregated Totals",
"name": "aggregatedTotals",
"kind": "Measure"
}

3. Include the aggregate measure in your dataViewMappings:

"dataViewMappings": [
{
"categorical": {
"categories": { "for": { "in": "metric" } },
"values": {
"group": {
"by": "date",
"select": [
{ "for": { "in": "total" } },
{ "for": { "in": "target" } },
{ "for": { "in": "kpi" } },
{ "for": { "in": "aggregatedTotals" } }
]
}
}
}
}
]

[v-saisrao-msft]

Hi @cchilton,

Thank you for reaching out to the Microsoft Fabric Forum Community. 

I understand that you're building a custom KPI table visual and facing a challenge in rendering both raw (unaggregated) data for sparklines and aggregated totals in a separate column. The issue arises because Power BI only activates one data View at a time, making it tricky to access both the raw data by date (for sparklines) and aggregated totals. 

To get the aggregated totals create a DAX measure like this: 

TotalSum = CALCULATE(SUM('YourTable'[Value]), ALL('YourTable'[Date])) 

This measure calculates the sum of the values across all dates, regardless of the date context, which is important for displaying the total in the Totals column. 

• Replace 'YourTable'[Value] with the column or measure you're using
• Replace 'YourTable'[Date] with the appropriate date column in your model. 

The capabilities.json file, ensure that you include both the raw data for sparklines (grouped by date) and the aggregated total (as a separate measure). Here’s how you can structure the dataViewMappings: 

{
  "dataViewMappings": [
    {
      "categorical": {
        "categories": {
          "for": { "in": "metric" }
        },
        "values": {
          "group": {
            "by": "date",
            "select": [
              { "for": { "in": "total" } },  // Raw data for sparklines
              { "for": { "in": "target" } },
              { "for": { "in": "kpi" } }
            ]
          },
          "select": [
            { "for": { "in": "totalSum" } }  // Aggregated total (e.g., TotalSum measure)
          ]
        }
      }
    }
  ],
  "dataRoles": [
    { "name": "metric", "kind": "Grouping", "displayName": "Metric" },
    { "name": "date", "kind": "Grouping", "displayName": "Date" },
    { "name": "total", "kind": "Measure", "displayName": "Total" },
    { "name": "target", "kind": "Measure", "displayName": "Target" },
    { "name": "kpi", "kind": "Measure", "displayName": "KPI" },
    { "name": "totalSum", "kind": "Measure", "displayName": "Total Sum" }
  ]
}

In your visual's TypeScript/JavaScript code, you can access both the raw data for sparklines and the aggregated total.

• Access the dataView.categorical.values.grouped() method to get the grouped data by date and iterate over them to build the sparkline. 
• Access the totalSum measure from dataView.categorical.values to get the total value, which you can display in the Totals column. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[cchilton]

I already tried to have 2 select statements like you've shown above. I get the below errors:
error should have required property '.for' .dataViewMappings[0].categorical.values
error should have required property '.bind' .dataViewMappings[0].categorical.values
error should match exactly one schema in oneOf .dataViewMappings[0].categorical.values
error Invalid capabilities

Do you have any advice?