Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anusha_2023
Helper II
Helper II

Power BI Matrix

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. 

 

anusha_2023_0-1717747609030.png

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?

 

 

 

 

1 ACCEPTED SOLUTION
Sergii24
Super User
Super User

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.

Sergii24_2-1717750816281.png

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.)

Sergii24_0-1717750599701.png

 

 

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:

Sergii24_3-1717750917950.png


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!

View solution in original post

2 REPLIES 2
Sergii24
Super User
Super User

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.

Sergii24_2-1717750816281.png

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.)

Sergii24_0-1717750599701.png

 

 

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:

Sergii24_3-1717750917950.png


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!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.