Automatic Visuals by use of Configuration Table

[askildu]

Hello!

I have an intereseting problem. I have data in one group of queries (DFU1, DFU2, DFU3, etc.), normalization data in another group (NormHours, NormFlights, NormPlatforms, etc.), and one single query to describe how the visuals should be configured (Graphics). From the raw data I need to present (not together, but dependent on what the visual configuration query says) the row count, sum of column "Weight", and sum of column "Weight" divided by normalization data (e.g., no. of working hours that year). The data used to be presented in Excel, and the Graphics query contains columns such as "PlotTitle", "NormDataSource", "NormAdjustFactor", "FilterDFU_Column", "FilterColumn1", "FilterColumn2" which contains all necessary info to make visuals in PowerBI.

My challenge is to make a slicer to choose which row to use in Graphics (each row contains a different visual configuration) and automatically make visuals ( I have a PerspectiveID column to be able to differentiate between the rows). All of the rows are made in the same way, just with different values (e.g., filter on DFU is DFU = 1 for ID1 and DFU = [7, 8, 9] for ID2). Excerpt from the Graphics query below, where the first two are values to include (=filter) and the third column is a factor to multiply the row count by (e.g., to get incidents per million working hours, instead of per working hour).

[lbendlin]

So you are thinking about creating your own meta language ? What's the business need?  Have your users asked for that?  

[askildu]

Not directly, no. I'm converting the data from Excel into PowerQuery/PowerBI and if it was relatively readily available, it would be nice to have these calculated automatically. I have about 30 rows in my Graphics query (most likely not to increase though), so although changes to these are not very likely, I'm slightly concerned that people assume changes will be made in dashboard if changes are made in this Excel table.

That being said, this is definitely not a must, so if this requires more than 20-30 hours, then it is not worth it

[lbendlin · Accepted solution]

It's a nice finger exercise.  (You should read about yacc and lex).  But yes, this will definitely take more than 20-30 hours. Much more.

[askildu]

Then I'll definitely leave it for another time. Thanks for your reply!