Power BI Matrix

[anusha_2023]

I have a requirement from the Finance team at the first end of the quarter year, they have Forecast 1 and, second quarter Forecast 2 is added and by the end of the third quarter, Forecast 3 is added. Conditionally these columns are visible based on columns that have values that otherwise need to be hidden. How can I achieve this kind of matrix design. 

Since currently only the Forecast1 column has values for 2024 only Forecast1 should be visible, Forecast2 and Forecast3 columns should be invisible. In the next month in July, Forecast2 values will be added so Forecast1 and Forecast2 values should be shown and Forecast3 should be hidden. On the other hand 2023, all Forecast 1, 2, and 3 columns have values so all three columns should be visible. Can I achieve this conditionally showing columns based on whether columns have data or not?

[Sergii24 · Accepted solution]

Hi @anusha_2023, it is a very good question, thanks for it! 

To show a measure at any visual you need to bring to drag and drop it a desired element on a canvas. By default, PowerBI will always show this measure (in your case as a column of matrix). The only thing you can control is measure's value, but the fact of having that column with measure name visible is undoubtable.

So what can we do? We need to use an element that allows us to change the number of dipalyed fields. The only one that comes to my mind is "Field parameter" (you can learn more about it here). By placing Forecast 1,2 and 3 in field parameter we can show more/less columns by using a filter.

So the last thing we need to do is to automatically apply filter to necessary values and here where the beaty is!

Remember, "filed parameter" is a table that you can access and, consequently add extra columns to it 😉
What can do is add a column "InScope" with true/false result that will define whether specific row of field parameter table should be considered or not. In my case I decided to apply the logic based on the fact if measure has any value or not (you can redesing it to show extra rows only after specific date or etc.)

InScope = 
VAR _CurrentKPI = Parameter[Parameter]
RETURN 
    SWITCH(
        _CurrentKPI,
        "Forecast 1", NOT ISBLANK( [Forecast 1] ),
        "Forecast 2", NOT ISBLANK( [Forecast 2] ),
        "Forecast 3", NOT ISBLANK( [Forecast 3] ),
        FALSE()
    )

  The final step, is to apply this filter with "true" filter applied on a visual:

Next time you refrehs semantic model , "InScope" will be updated acordingly so more rows might have true condition (depending on the logic in "InScope").

Few considerations:

• Make sure to block this filter, you don't want users to change its value
• I've used measures in calucalted column, you might want to use static value to make sure its behavior doesn't change depending on filter context

You can find sample file attached. Try to change Forecast 2 value to a static number to see this logic in action.

Good luck with your project!

[anusha_2023]

Thank you very much for the quick and very detailed solution. It serves the purpose exactly. Still, I am facing an issue with the Calculated column "Inscope" that has been added to the Parameter Table. My measures were responding to measures instead of calculated column Inscope. Tried to make an "Inscope Measure" but could not succeed. Could you please check the issue? I uploaded the Pbix file which was modified by using your file only. 

https://drive.google.com/file/d/1JDAIo3R1W-45I4oK4HgOvjXAbws3A5kC/view?usp=sharing