0

I have a table with multiple company data and having company_id column in it. I want to show the slider with maximum value of selected company_id for ranking feature to the report.

I have created a measure to calculate count for the maximum value of selected company.

Max Value = CALCULATE(DISTINCTCOUNT(SOURCE_TABLE[Company_Name]),FILTER(SOURCE_TABLE,SOURCE_TABLE[Company_id]=SELECTEDVALUE(SOURCE_TABLE[Company_id])))

But when I try to use this for What-if parameter in ranking feature getting below error,

enter image description here

TopRank = GENERATESERIES(1,[Max Value], 1)

Error Code:

The arguments in GenerateSeries function cannot be blank.

When I use DISTINCTCOUNT function it works fine, but with filter it had an issue.

TopRank = GENERATESERIES(1,DISTINCTCOUNT(SOURCE_TABLE[Company_Name]), 1)

-- This is working fine

How can I get the value of DISTINCTCOUNT with FILTER. Any ideas will be appreciated.!

Wanna Coffee
  • 2,742
  • 7
  • 40
  • 66

1 Answers1

2

As you may have noticed, What-If parameter table is a calculated table.

Calculated table is re-calculated only when you have refreshed the data source. It will not be re-calculated based on user interaction, such as selecting a company ID in the slicer.

In your scenario, you need to create the parameter table with a possible maximum number. And, you may filter the slicer so that it only shows the available numbers only based on the currently selected company ID.

You can do that by creating a measure which indicates whether the parameter is available for the currently selected company ID. For example,

__AvailableRankFlag = IF( [TopRank Value] <= [Max Value], 1 )

and add this measure to filter of the slicer with "__AvailableRankFlag is 1".

Kosuke Sakai
  • 2,336
  • 2
  • 5
  • 12