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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

User Input is not recognized in DAX formula

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:

 

Report_Amount = SWITCH(TRUE(),
AND([SOURCE] = "VW1", [PER] <= 3), [AMOUNT],
AND([SOURCE] = "VW2", [PER] = 3), [AMOUNT], 0)
 
In this example, the Report amount is perfectly correct for Period 3 because I hard coded #3 in there.
But when I replaced 3 with [Mon_Num_Ref], then all amounts become zero.
 
Next I forced a numeric value to be sure using VALUE([Mon_Num_Ref]) but it didn't help.
Then I tried VALUE([Account_Display]) which is a formula column which has alpha characters in it but it
complain and returns all amounts as zero as before.
 
So apparently the formula does not even try to read these new formula columns. But it does read correctly
the original columns from the Query1 meaning if I replace 3 with [Fiscal_Year] (with values of 2019,
2020..), which is larger than 12, all amounts show up correctly. My observation is in this
dashboard table, the User Input value and its related formula columns are not read by the formula above.
 
What am I doing wrong and what's your recommendation?
Thank you.
Daniel
9 REPLIES 9
v-luwang-msft
Community Support
Community Support

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

v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Try to test the below:

Step1,create silcer table for per:

vluwangmsft_0-1638258233273.png

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)

vluwangmsft_1-1638258273068.pngvluwangmsft_2-1638258283858.png

vluwangmsft_3-1638258293653.png

 

You could download my pbix file if you need.

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Anonymous
Not applicable

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:

SourcePERValue
VW113
VW1264
VW121
VW223
VW2321
VW2431
VW253

 

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

Anonymous
Not applicable

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:

SourcePERValue
VW113
VW1264
VW121
VW223
VW2321
VW2431
VW253

 

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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.

AlexisOlson
Super User
Super User

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.

Anonymous
Not applicable

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. PBI_Measure.jpg

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.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors