Table should display values greater than the value selected in filter slicer.

[shubh_kush]

Hi Everyone, I have a query regarding PowerBI.

I have a column in the table <Cost_Percentage> with values (10, 15, 25, 35, 45, 55...till 95).

I need to create a column for the slicer <Filter_Percentage> which will have values like 0, 5, 10, 15, 20, 25, 30, 35,....90, 95.

My requirement is, when user selects, say 20 from Filter_Percentage in the slicer, then Cost_Percentage (in the table) should display values greather than 20 till 95.

Example, 

IF Filter_Percentage is 10, then table should display values of Cost_Percentage greater than 10 only (>10)

IF Filter_Percentage is 20, then table should display values of Cost_Percentage greater than 20 only (>20)

I tried creating calculated column with if condition using DAX, but that replaced all the values with the first expression only.

Thanks in Advance.

Shubham

[v-robertq-msft]

Hi, @shubh_kush 

I suggest you to use the what-if parameter to work with the silcer instead of creating a new column.

1. click the new parameter in modeling
2. enter the parameter name and properties, then click “Add silcer to the page”, follow this picture:

3. create a new measure:

Measure =

CALCULATE(COUNTROWS('Table'),

FILTER('Table',

'Table'[Cost_Percentage]>SELECTEDVALUE(Filter_Percentage[Parameter])))

4. create a table in report , add Cost_Percentage and measure to value, you can get what you want

Best Regards,

Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shubh_kush]

@v-robertq-msft , @amitchandak 

Thanks for your help guys. It made my concept clear how to harness the values. I am able to achieve this by creating a differet table with the percentage values and using the values in the measure thorugh slicer.

But now with the same method, I landed in a different scenario. 🙂

I have a column Cost_Percentage (from the dataset) and a measure Base_Percentage (which i created).

DAX for Base_Percentage is as follows:

Base_Percentage =  IF(HASONEVALUE('Table_A'[Percentage_Base_Change]), VALUES('Table_A'[Percentage_Base_Change]), 0)

I am using the above Base_Percentage measure in the slicer so that user can select the specific percentage. 

I want to subtract Cost_Percentage and Base_Percentage (Whatever user selects).

Say if user selects Base_Percentage = 30 from the slicer,  then values in the column Cost_Percentage should be subtracted by 30.

But I cannot use Cost_Percentage with Base_Percentage in the newly created measure directly .

Say, Measure_1 = (Cost_Percentage - Base_Percentage),  How can I read all single row values of the column Cost_Percentage and subtract it from the measure Base_Percentage whatever user select from the slicer?

The logic is quite large but for now I just want to subtract and know how can I use the column in the measure for some mathematical expression.

Thanks

[v-robertq-msft · Accepted solution]

Hi, @shubh_kush 

According to your requirement, you can try this measure:

Measure_1 = SUMX('Table',[Cost_Percentage]-[Base_Percentage])

This is my test pbix file:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EeCE2fdcxutFnMG94...

And you can get what you want, like this:

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shubh_kush]

@v-robertq-msft Thanks for your solution. I tried your method with some tuning in the dataset and its working fine.  I used your logic but since the SUMX was there, I had to divide the values by the category in order to get the exact values since there was a grouping with the category and so the data was not correct as per the logic.

Thanks you so much 🙂

[v-robertq-msft]

Hi, @shubh_kush 

What is your category about? would you like to post some sample data(without sensitive data) in the form of table and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

[amitchandak]

@shubh_kush , Assuming it is a column and not measure. I would better suggest to create a what parameter and use that filter instead of this column

You can have measures like

calculate(countrow(Table), Filter(Table, Table[Cost_Percentage] >= selectedvalue(Cost[cost])))

or

measure =
var _min = minx(allselcted(Cost),cost[cost])
return
calculate(countrow(Table), Filter(Table, Table[Cost_Percentage] >= _min))

refer for what if

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[shubh_kush]

@amitchandak , I tried you solution, created the measure after implementing What-if with 5 intervals. Upto this everything was great, as I was able to use the slicer and slicer was trying to filter the data in the table when I put the measure. But measure is not showing the actual values but 99 😞

[amitchandak]

@shubh_kush ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.