3
$\begingroup$

I need to be able to write custom fields in Power BI but the values of the fields change based off of company. For example, Twitter vs Instagram - field 1 for Twitter should show company name and field 1 for Instagram should show company revenue. So not only does the field change but the value of the field changes too. I have an excel sheet that lists around 30 custom fields for 2 different companies so I was wondering what the easiest way around that would be. Thanks!

$\endgroup$
2
  • 1
    $\begingroup$ Are the types of values in field 1 for each company going to be the same over time? So, field 1 for Twitter will always be company name, etc.? $\endgroup$
    – Upper_Case
    Commented Apr 19, 2019 at 20:12
  • $\begingroup$ Field 1 for Twitter will always be company name but field 2 for Instagram could be date $\endgroup$
    – Noureen
    Commented Apr 22, 2019 at 13:59

1 Answer 1

0
$\begingroup$

I'm by no means a PowerBI expert, so there may be options I'm not aware of. I don't think that there is a tidy solution to this in PowerBI, as this is not quite what BI tools are usually intended to do. However, it can be done-- you'll just have to accept a degree of hack/kludge character to your projects.

The most straightforward approach I can think of is to make a new table for your presentation values, with something like a column for each company and then another column for "meaningful data to present". That latter column should be set to something like text, so that it will display different values with the correct conventions (dollar signs, comma placement, etc.). Then, when drawing on data to populate the report, set the card (or whatever structure you're using) to draw from that presentation table. Since field1 will always contain the correct data to display for a given company, and that field is formatted as text, you won't have to worry about mismatched data types.

This approach will require some preprocessing of your data, so that the presentation table is populated correctly for each distinct company whose data you are presenting, but it's nothing that a SQL query can't produce pretty easily. This can be done within PowerBI or externally, with the presentation table imported. Importantly, you will not have the option of using aggregations or similar operations (that would be the case anyways, with this type of report output, but it's worth mentioning explicitly). This will be, essentially, a display-only report.

The key is that field1 (using the naming convention in the question) is representing something unstable in the data itself (different data types in the same field), but relevant in an "above the data" sense.

As a final note, I'll encourage you to consider whether or not this data really belongs in a single, conglomerated report. That might legitimately be the case, or it may be that this structure is demanded by a superior (whether it makes sense or not). But maintaining a single, kludge-filled report like this might ultimately be more work (and less valuable) than multiple reports which fit better with PowerBI's design favoring strongly-typed data fields.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.