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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
benjos23
Helper I
Helper I

Check if nothing is selected in a matrix

Hi !

In my Power BI report, I have a matrix that shows the monthly income statement of a company. More specifically, it shows for each important indicator (such as sales, gross margin, ebit, ...) the actual amount, the budgeted amount, variation, ..., with a per month view :

benjos23_3-1737707787347.png

Values make no sense because they are randomized 🙂


My KPIs table contains all the KPI name, with a position column so that the matrix can be correctly sorted :

benjos23_4-1737707885029.png

 

On my page, I also have other visuals that shows interesting insights, such as the evolution per month, and that can be filtered according to what is being selected in the matrix.
Problem is that, when nothing is selected in the matrix, the total is automatically computed and showed in other visuals. However, this total is analytically wrong, as it should simply take the last indicator total in the matrix, e.g. the cashflow.
I have been able to change my ACT and BGT measures, so that they are filtered on last available KPI when SELECTEDVALUE(KPIs[Position]) is BLANK :

ACT_MIS =
VAR LastKPIPosition = MAX(KPIs[Position])
RETURN
IF(ISBLANK(SELECTEDVALUE(KPIs[Position])), CALCULATE([ACT], KPIs[Position]==LastKPIPosition), [ACT])


However, the condition ISBLANK(SELECTEDVALUE(KPIs[Position])) will also be evaluated to TRUE when many indicators are selected in the matrix, which I don't want...
Thus, here comes my question :
- Is there a way to specifically check if many values of a certain column are being selected in the matrix ? My ACT_MIS measure should return the last available KPI actual value ONLY when nothing is being selected in the matrix. I want to allow the user to have the freedom to select many indicators so that the total is computed (e.g. maybe he would want to know the total of production costs + administration costs).

I would really appreciate your help.

1 ACCEPTED SOLUTION
Sergii24
Super User
Super User

Hi @benjos23, I'll be honest, saying I've been lost a bit in your case description... However, focusing on a final question:

"Is there a way to specifically check if many values of a certain column are being selected in the matrix?"

 

The answer is Yes.

  • You can use ISFILTERED – DAX Guide in case you want to check if any "direct" filter is applied on a column.
  • In case you are interested in indirect filter (i.e., a filter is applied to any column of the same table or in a related table), take a look at ISCROSSFILTERED – DAX Guide
  • Consider that you won't be able to distinguish "Select All" from "Nothing is selected" as the result query would be the same

Once you use one of these 2 functions, you can combine them with SELECTEDVALUE(), which will return a result only if a single value is selected. So: if column is filtered (directly or indirectly) and SELECTEDVALUE = BLANK() (i.e., more than one vale is selected), you can define the desired behavior.

 

Good luck with your project! 🙂

View solution in original post

3 REPLIES 3
Bibiano_Geraldo
Super User
Super User

Hi @benjos23 ,

You can use the HASONEVALUE() function to check if only one KPI is selected in the matrix. This way, if no value is selected or if multiple values are selected, you can apply the logic for taking the last available KPI.

 

Adjust your ACT_MIS measure to look like this:

ACT_MIS =
VAR LastKPIPosition = MAX(KPIs[Position])
RETURN
IF(
    NOT HASONEVALUE(KPIs[Position]), 
    CALCULATE([ACT], KPIs[Position] = LastKPIPosition),
    [ACT]
)
Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue
Sergii24
Super User
Super User

Hi @benjos23, I'll be honest, saying I've been lost a bit in your case description... However, focusing on a final question:

"Is there a way to specifically check if many values of a certain column are being selected in the matrix?"

 

The answer is Yes.

  • You can use ISFILTERED – DAX Guide in case you want to check if any "direct" filter is applied on a column.
  • In case you are interested in indirect filter (i.e., a filter is applied to any column of the same table or in a related table), take a look at ISCROSSFILTERED – DAX Guide
  • Consider that you won't be able to distinguish "Select All" from "Nothing is selected" as the result query would be the same

Once you use one of these 2 functions, you can combine them with SELECTEDVALUE(), which will return a result only if a single value is selected. So: if column is filtered (directly or indirectly) and SELECTEDVALUE = BLANK() (i.e., more than one vale is selected), you can define the desired behavior.

 

Good luck with your project! 🙂

Hi Sergii, it effectively does what I want ! Thank you very much !

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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