Data converted to wide table in Report and use parameter in a slicer for said table

[Anonymous]

My PowerBI dashboard is mainly run off a long table. In one report page, I show a wide table (using a matrix) with the variable being an analyte, and the columns being the counts and mean value of the predicted vs actual for the analyte.

Wide table shown in report using Matrix:

Variable    Pred.count   Pred.mean   Actual.count   Actual.mean

analyte1   

analyte2

...

The data coming in from the dashboard looks like:

Variable   Value  Type

analyte1   xxx     Pred

analyte1   xxx     Actual

....

I want the table to only show values where there are atleast x of "Actual.count". I can create a parameter and use it in a table, but that doesn't work when my matrix is based off a long table. So I think I need to first convert it to a wide table somewhere in the report (so my filters still work), then create a parameter with a slicer for it.

Any ideas on how to do this? Thank you.

[Anonymous]

Hi @Anonymous ,

Converting from these 4 to the 2 below is unlikely, but the Unpivot function can achieve a similar effect.

In Power Query, Select all 4 columns at the same time, click Unpivot Columns.

The final output is below:

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

[Anonymous]

Would I first duplicate my main table? Then run the pivot on the new table? I still need the long table format for all the other pages of my report. Also, my data is already in the long format, so it is like your "output table" in your example. I want to go the other way, but still have the individual values have filters applied before summing in the wide table.

When I duplicated the table, I am not even sure how to get to Power Query in it. In your screenshots, you cut off the code at the top of the table, so I can't see the DAX code to create the tables.

Another problem is that once I pivot the long table, I lose all the dates associated with the entries, so then the filters won't work. That's why I was hoping to have the pivoted table in the report page, so all the filters still work on the data going into that pivoted table from the long table. Thanks.