"Advanced" Filtering data in a report

[daxyco]

I have a large amount of similarly formated data that I need to be able to filter out per request while displaying a report (so not in Power Query). The data is related to power plants, many of them. The data consists of a large number of *.csv files that all have values for 30 years (planning period). Powerplants all belong to an electrical grid node, are of a certain type of tech (wind, solar, thermal...), use a certain kind of fuel (gas, black coal, brown coal...), etc.

I need to be able to show visuals for the sum of all powerplants in question, but also to do some filtering per any of these properties I listed. So, I might need to filter out all powerplants belonging to a certain node, or thermal powerplants that use black coal as fuel, etc. I have noticed that slicers can do what i need, but in my case they only do filtering by year, because all my data is structured so as that the first row is the header row, and then the values for each year of the planning horizon follow. But I also need to keep filtering by year. Is there a way to do this? I'm attaching the sample of my data, as well as the "dimension table" that carries the information about all of the powerplants that are relevant for my use case.

Thanks in advance for any help,

Danilo

[daxyco]

That's exactly what I needed, thanks! The visual I posted was from another table, that's why there was a difference for each year.

[v-yadongf-msft]

Hi @daxyco ,

Is this the result you want?

The value for every year is the same.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft · Accepted solution]

Hi @daxyco ,

This is my test table1:

Test table2:

Transform table2 in Power Query. Select Date column - Transform tab - Unpivot other columns.

You will get a table like this, close and apply to Power BI:

Create one to many relationship between two test tables:

Create some slicers from table2 and create a card visual from table1:

You can filter report by any of these properties:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daxyco]

@v-yadongf-msft seems to be working, thanks! I know I might be asking for too much now, but is it possible to somehow keep this type of view in stacked column chart visual? This way I can already see how much each type of plant contributes to total generation, but without having their generation in separate columns, I can't see a way of creating this kind of a visual. I guess I could create two separate queries, one being unpivoted as you mentioned, one pivoted (as the original data) and have two separate visuals for each of them, but I'm guessing the people looking at the report would like to see as much detail in one chart instead of having to look at two different charts.