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

Need Help understanding Matrix dataMapping

Hey All, 

 

I have this dataset: 

Gym NameDepartment NameTotal SalesTotal ProfitTopUpperTargetLowerBottomLower ColorLower Middle ColorMiddle ColorMiddle Upper ColorUpper Color
YMCAMembership Sales100075010007505002500#dc3c2e#e57d2d#ffffff#7f1f86#0094e2
YMCAApparel Sales1007510075502500#dc3c2e#e57d2d#ffffff#7f1f87#0094e3
YMCAEquipment Sales50025025012575250#dc3c2e#e57d2d#ffffff#7f1f88#0094e4
Gold's GymMembership Sales2000125025002000150010000#ffffff#ffffff#000000#e3cf0b#e3cf0b
Gold's GymApparel Sales2001501007550150#ffffff#ffffff#000001#e3cf0b#e3cf0b
Gold's GymEquipment Sales100030002000150012501000 #ffffff#ffffff#000002#e3cf0b#e3cf0b

 

 

And below is my Data Mapping, specified in my capabilities.json file:

{
    "dataRoles": [
        {
            "displayName": "Columns",
            "name": "column",
            "kind": "Grouping"
        },
        {
            "displayName": "Rows",
            "name": "row",
            "kind": "Grouping"
        },
        {
            "displayName": "Values",
            "name": "values",
            "kind": "Measure"
        },
        {
            "displayName": "Lower Threshold Value",
            "name": "lowerThreshold",
            "kind": "Measure"
        },
        {
            "displayName": "Lower-Middle Threshold Value",
            "name": "lowerMiddleThreshold",
            "kind": "Measure"
        },
        {
            "displayName": "Middle Threshold Value",
            "name": "middleThreshold",
            "kind": "Measure"
        },
        {
            "displayName": "Middle-Upper Threshold Value",
            "name": "middleUpperThreshold",
            "kind": "Measure"
        },
        {
            "displayName": "Upper Threshold Value",
            "name": "upperThreshold",
            "kind": "Measure"
        },
        {
            "displayName": "Lower Threshold Color",
            "name": "lowerColor",
            "kind": "Measure"
        },
        {
            "displayName": "Lower-Middle Threshold Color",
            "name": "lowerMiddleColor",
            "kind": "Measure"
        },
        {
            "displayName": "Middle Threshold Color",
            "name": "middleColor",
            "kind": "Measure"
        },
        {
            "displayName": "Middle-Upper Threshold Color",
            "name": "middleUpperColor",
            "kind": "Measure"
        },
        {
            "displayName": "Upper Threshold Color",
            "name": "upperColor",
            "kind": "Measure"
        }
    ],
    "objects": {
        "dataPoint": {
            "displayName": "Data colors",
            "properties": {
                "defaultColor": {
                    "displayName": "Default color",
                    "type": {
                        "fill": {
                            "solid": {
                                "color": true
                            }
                        }
                    }
                },
                "showAllDataPoints": {
                    "displayName": "Show all",
                    "type": {
                        "bool": true
                    }
                },
                "fill": {
                    "displayName": "Fill",
                    "type": {
                        "fill": {
                            "solid": {
                                "color": true
                            }
                        }
                    }
                },
                "fillRule": {
                    "displayName": "Color saturation",
                    "type": {
                        "fill": {}
                    }
                },
                 "fontSize": {
                    "displayName": "Text Size",
                    "type": {
                        "formatting": {
                            "fontSize": true
                        }
                    }
                }
            }
        }
    },
    "dataViewMappings": [
        {
            "matrix": {
                "rows": {
                    "select": [
                        {
                            "for": {
                                "in": "column"
                            }
                        }
                    ]
                },
                "columns": {
                    "for": {
                        "in": "row"
                    }
                },
                "values": {
                    "select": [
                        {
                            "for": {
                                "in": "values"
                            }
                        },
                        {
                            "bind": {
                                "to": "lowerThreshold"
                            }
                        },
                        {
                            "bind": {
                                "to": "lowerMiddleThreshold"
                            }
                        },
                        {
                            "bind": {
                                "to": "middleThreshold"
                            }
                        },
                        {
                            "bind": {
                                "to": "middleUpperThreshold"
                            }
                        },
                        {
                            "bind": {
                                "to": "upperThreshold"
                            }
                        },
                        {
                            "bind": {
                                "to": "lowerColor"
                            }
                        },
                        {
                            "bind": {
                                "to": "lowerMiddleColor"
                            }
                        },
                        {
                            "bind": {
                                "to": "middleColor"
                            }
                        },
                        {
                            "bind": {
                                "to": "middleUpperColor"
                            }
                        },
                        {
                            "bind": {
                                "to": "upperColor"
                            }
                        }
                    ]
                }
            }
        }
    ]
}

 I couldnt post my matrix object due to the size, but I get 24 values within each child element under the root, and the values for those children are "" (blank).C an anyone actually help me understand what is going here? My do my values all have a value of ""? When I only have one "gym" (YMCA) of data in my data set, it works fine, however, with two rows of data, it acts all janky.

 

Any help is greatly appreciated!

Thanks!

5 REPLIES 5
Community Champion
Community Champion

Hello @paynemiller,

 

I'm wondering why you prefer matrix than table data mapping. Could you please describe your case?

I suppose that it'd be better to use table data-mapping instead.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

@v-viig Well, we are trying to achieve an extension of the current "out-of-the-box" Matrix provided by Power BI. We would like to be able to color code certain fields based on mathematical conditions. I reviewed the Data Mapping Documentation at GitHub, however, there is no matrix section. I assume the matrix data mapping is how we achieve the matrix vizulaization, we need multiple columns, multiple rows, all with related values.

Thanks for the clarification.

Could you please share your data-set to investigate the issue with data-mapping?

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Sure thing!

Below is my data set (imported into PowerBI from Excel)

 

Gym Name	Metric Name	Metric Value	Top	Upper	Target	Lower	Bottom	Lower Color	Lower Middle Color	Middle Color	Middle Upper Color	Upper Color	ID
Gym1	Membership Sales	1000	1000	750	500	250	0	#dc3c2e	#e57d2d	#ffffff	#7f1f86	#0094e2	1
Gym1	Apparel Sales	100	100	75	50	250	0	#dc3c2e	#e57d2d	#ffffff	#7f1f87	#0094e3	1
Gym1	Equipment Sales	500	250	125	75	25	0	#dc3c2e	#e57d2d	#ffffff	#7f1f88	#0094e4	1
Gym2	Membership Sales	2000	2500	2000	1500	1000	0	#ffffff	#ffffff	#000000	#e3cf0b	#e3cf0b	2
Gym2	Apparel Sales	200	100	75	50	15	0	#ffffff	#ffffff	#000001	#e3cf0b	#e3cf0b	2
Gym2	Equipment Sales	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2

Capture.PNGThis is dataView object that has been got using your capabilties and data-set.

There're no null values.

 

Are you still able to reproduce the issue? If so, please provide extra repro steps if that 's possbile.

 

 

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors