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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
oalgarvio
Frequent Visitor

Using variable slicer to filter multiple columns of a table

I need a dynamic slicer (week number of the year) to filter 6 columns (6 charts) of values above the slicer value.

 

I tried to create a parameter to use in the slicer with a starter value of 1, end value of 53 and an increment of 1 with a default value of 53: Parameter Week = GENERATE SERIES(1, 53, 1) Parameter Week Value = SELECTED VALUE('Parameter Week'[Parameter Week], 53)

and with that created a new column like: Column= IF(table1[column A]>= Parameter Week [Parameter Week value],1,0).

 

I put a card to give the actual value of the Parameter Week value which is currently at 37 and the values are being filtered at >=53 which is the default value. What am I missing?

 

I tried to use a measure instead of a column like: Measure = IF(table1[column A]>= Parameter Week [Parameter Week value],1,0)

but I'm getting an error of: A single value for column 'Column A' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

TIA

1 ACCEPTED SOLUTION

I will throw this out there...What if you try SUMX?

 

Measure = IF(SUMX(table1,table1[column A])>= Parameter Week [Parameter Week value],1,0)

View solution in original post

4 REPLIES 4
MP-iCONN
Resolver I
Resolver I

I might be way off but wouldn't the Parameter Week Value measure be this....

 

Parameter Week Value = SELECTEDVALUE('Parameter Week'[Parameter Week], 53)

That is how it is, I copied and pasted and forgot to change, sorry, corrected now

I will throw this out there...What if you try SUMX?

 

Measure = IF(SUMX(table1,table1[column A])>= Parameter Week [Parameter Week value],1,0)

That is working when I display it in a table but not in a donut chart, which is counting the values of a specific product. In the table I only have an entry (SUMX(table1,table1[column A])>= 37) of a product A, the donut chart should be giving me a value of 1 product A, but it's giving me 22 products A, 22 product B, 21 product C and 8 products D, which is the values without the filter.

 

I manage to resolve that. You got me the solution, thank you.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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