Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi all,
I am a newbie and want to create a report with different sets of criteria, based on user input. The input value appear fine on the dashboard and in the report table but in formulas, it seems to be ignored. Here is what I mean. In one data source Query1, there are data belonging to VW1 and VW2 Sources.
Data Source VW1 needs to sum up all amounts from period 0 to the month specified by the user
Data Source VW2 needs to display amount for only one month specified by user, same input.
I created a table "Month" using "Enter Data" and put in periods 0 - 12 in a column called Mon_Num. Next I created a slicer so a user can choose a period. In this example, the user chooses period 3.
The ledger from a table called Query1 is just a table format with FY, period and amount... I created a new column called Mon_Num_Ref which is defined as SELECTEDVALUE('Month'[Mon_Num]) which is just a column to look at the value for the user input. The dashboard table displays all the fields including Mon_Num_Ref correctly that shows the User Input value for the month specified which in this example is 3. So everything works correctly up to this point.
Next I created a new amount column called Report Amount and the formula is:
Hi @Anonymous ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien
Hi @Anonymous ,
Try to test the below:
Step1,create silcer table for per:
Then use the below:
test1 = SWITCH ( TRUE (),
(SELECTEDVALUE('Table'[source])= "VW1"&& SELECTEDVALUE('Table'[PER])<= SELECTEDVALUE(preslicer[preslicer])), [measure1],
( SELECTEDVALUE('Table'[Source])= "VW2"&&SELECTEDVALUE('Table'[PER]) = SELECTEDVALUE(preslicer[preslicer])) , [Measure2] , 0 )
return:(return value according measure1 ,and measure)
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi Lucien,
Thank you for trying to help. I downloaded your file, simplified it to clarify what the expected results should be but the numbers in the resulting table don't reflect what I was looking for. Maybe I am missing something here so if you can review this with me. If we focus on just VW1 and VW2 to make it simple, here is the data from your test table:
Source | PER | Value |
VW1 | 1 | 3 |
VW1 | 2 | 64 |
VW1 | 2 | 1 |
VW2 | 2 | 3 |
VW2 | 3 | 21 |
VW2 | 4 | 31 |
VW2 | 5 | 3 |
Further, if we can remove the PER and Source slicers, leaving only preslicer and the table in your dashboard. If a user select 1 in the preslicer, we would need to see the following values for VW1 and VW2:
For VW1, we want all periods less than or equal to the selected period, in this case 1. For VW2, we only want period selected, 1. So VW1 should have the value of 3 and VW2 should have 0.
When the preslicer is 2, VW1 should be (3 + 64 + 1) = 68. VW2 should be 3.
When the preslicer is 3, VW1 should still be 68 since there is no period 3. VW2 should be 21.
When the preslicer is 4, VW1 should still be 68 since there is no period 3 or 4. VW2 should be 31.
When the preslicer is 5, VW1 should still be 68 since there are no period 3 through 5. VW2 should be 3.
I did modify the formular test1 but could not get the numbers that I am looking for.
Thank you so much for your interest.
Daniel
Hi Lucien,
Thank you for trying to help. I downloaded your pbix and after some exploring, I made a few changes just to clarify what I am trying to do. If we'd just focus on VW1 and VW2 in the sample data that you created and for which formula you created, we would expect the following values for the test1 measure. And for the sake of simplicity, we will delete the Source and Per slicers so we will be left with just the preslicer and the table.
To make it simple, I have captured your test data for VW1 and VW2 here to make it easy to follow:
Source | PER | Value |
VW1 | 1 | 3 |
VW1 | 2 | 64 |
VW1 | 2 | 1 |
VW2 | 2 | 3 |
VW2 | 3 | 21 |
VW2 | 4 | 31 |
VW2 | 5 | 3 |
If we select 1 in the preslicer, we want to add up all the values for periods less than or equal to 1 for VW1 and for VW2, only values equal to period 1. So VW1 should have 3 because there is only one record for VW1 and period 1 and that value is 3. VW2 should have 0 because there is no record for period 1. In other words, selecting 1 in the preslicer should result in VW1 = 3 and VW2 = 0.
If we select 2 in the preslicer, meaning periods 1 + period 2 for VW1, VW1 should be (3+ 64 + 1) = 68. VW2 value for period 2 should be just 3.
If we select 3 in the preslicer, we have periods 1 + period 2 but no period 3 for VW1 so VW1 should still be (3+ 64 + 1) = 68, VW2 should be 21, which is for period 3 only.
Likewise, if we select 4 in the preslicer, we want period 1 -> 4 for VW1, which is still 68 and for VW2, period 4, it is 31.
Selecting 5 in the preslicer, we want period 1 ->5 for VW1, which is still 68 and for VW2, period 5, it is 3.
This is the measure formula that I was trying to work with
Report_Amount = SWITCH(TRUE(),
AND([SOURCE] = "VW1", [PER] <= xxx), [AMOUNT],
AND([SOURCE] = "VW2", [PER] = xxx), [AMOUNT], 0)
where xxx is the preslicer value that the user would select. But so far all my attempts to make the formula to look at this preslicer value failed.
Thank you so much for your interest.
Daniel
Hi Lucien,
Thank you kindly for your help. I am currently on vacation but as soon as I get back to work, I will try out right away your suggestion.
Very much appreciated.
Daniel
I forgot to mention that we have done exactly what we need to do but it is on the desktop version where we have parameters or changing the SQL in the report that controls the logic going back to the database source, i.e. "WHERE (SOURCE = 'VW1' and PER <= &&PERIOD) OR (SOURCE = 'VW2' and PER = &&PERIOD)". But by doing that, we need to have that desktop going through the firewall to the database source.
Now we are exploring using the online version where users only have access through the gateway and do not have access to parameters that control the criteria of the data query. All logics must be contained within Power BI and this task has been a challenge, hence my post. Can this be done in Power BI?
The end result I am trying to achieve is the total of the amounts for all periods from zero to X for certain type of records in the main query. And for some other types of records in the same query, I want the amount for only that period X where X is selected in the slicer by the user. Any pointer to how this is best done will be much appreciated. Thank you.
Calculated columns cannot be responsive to user interactions like slicers. This is because they are calculated only once per time the data model is loaded or refreshed rather than in response to slicers. Once the model is loaded, they are fixed.
Measures, on the other hand, are intended to be dynamic and responsive to interactions. SELECTEDVALUE ( ... ) works just fine with measures.
Hi Alexis,
You are right on both counts. I misstated earlier about the column, the Mon_Num_Ref is actually a measure that looks at the SelectedValue of the slicer.
And it works correctly with every change in selected input value.
The Report Amount is a new column. It only works correctly with hard-coded values such as 3 and it works correctly with the original columns of Query1. But it ignores the new measures that I defined such as Mon_Num_Ref.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the September 2025 Power BI update to learn about new features.