Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 :
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 :
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 :
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.
Solved! Go to Solution.
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.
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 @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]
)
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.
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 !
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |