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
paynemiller
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
v-viig
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
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