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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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