topic Need Help understanding Matrix dataMapping in Developer

Need Help understanding Matrix dataMapping

paynemiller — Fri, 04 Aug 2017 18:41:38 GMT

Hey All,

I have this dataset:

Gym Name	Department Name	Total Sales	Total Profit	Top	Upper	Target	Lower	Bottom	Lower Color	Lower Middle Color	Middle Color	Middle Upper Color	Upper Color
YMCA	Membership Sales	1000	750	1000	750	500	250	0	#dc3c2e	#e57d2d	#ffffff	#7f1f86	#0094e2
YMCA	Apparel Sales	100	75	100	75	50	250	0	#dc3c2e	#e57d2d	#ffffff	#7f1f87	#0094e3
YMCA	Equipment Sales	500	250	250	125	75	25	0	#dc3c2e	#e57d2d	#ffffff	#7f1f88	#0094e4
Gold's Gym	Membership Sales	2000	1250	2500	2000	1500	1000	0	#ffffff	#ffffff	#000000	#e3cf0b	#e3cf0b
Gold's Gym	Apparel Sales	200	150	100	75	50	15	0	#ffffff	#ffffff	#000001	#e3cf0b	#e3cf0b
Gold's Gym	Equipment Sales	1000	3000	2000	1500	1250	1000		#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!

Re: Need Help understanding Matrix dataMapping

v-viig — Mon, 07 Aug 2017 08:20:53 GMT

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

Re: Need Help understanding Matrix dataMapping

paynemiller — Mon, 07 Aug 2017 12:22:38 GMT

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

Re: Need Help understanding Matrix dataMapping

v-viig — Tue, 08 Aug 2017 11:56:17 GMT

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

Re: Need Help understanding Matrix dataMapping

paynemiller — Thu, 10 Aug 2017 13:31:14 GMT

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

Re: Need Help understanding Matrix dataMapping

v-viig — Fri, 11 Aug 2017 08:13:41 GMT

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