cancel
Showing results for
Did you mean:
Occasional Visitor

## DAX: Getting slicer value into a calculated column in unrelated table?

I am using R to create some charts in Power BI, and one of the requirements is to only show the data points under a certain percentile that the user will pick from using a slicer.

My report has the following tables and there are no relationships defined:

Data

------

Name

Num Work Days

Percentiles

--------------

Percentile

The Percentile is used in a slicer on the report.

I wanted to get the selected percentile into the Data table so I can pass it to my R function, so I wrote this into a calculated column in the Data table:

SelectedPercentile = IF (DISTINCTCOUNT('Percentiles'[Percentile])=1, FIRSTNONBLANK('Percentiles'[Percentile], 1), "All")

But this always returns "All" regardless of the Slicer selection!

What is the correct way to get the selected slicer value into an unrelated table?

2 REPLIES 2
Frequent Visitor

## Re: DAX: Getting slicer value into a calculated column in unrelated table?

I may not be able to get you all the way down the road without knowing more, but here's how I would go about making a disconnected slicer work:

1. Create a measure like so:

PercentileSelection=MAX(Percentile[Percentiles)

2. Then, create a calculated column in your Data table that calculates the percentile (perhaps using the PERCENTILEX.INC formula)

3. Then, create a measure like this:

DataBelowSelectedPercentile:

CALCULATE (

COUNTROWS('Data'), FILTER('Data', [PercentileCalculationColumn] <= [PercentileSelection])

Hope this helps. Let me know if there's something more specific that needs to be addressed in your dataset.

)

Super User

## Re: DAX: Getting slicer value into a calculated column in unrelated table?

see first thing how the calculate or normal table works depends on unrelated interaction ?

once u created the calculated table its wont interact with the filters on original table.