cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
confused_kid Occasional Visitor
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
bourquejeff Frequent Visitor
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
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.