Power BI : Filters in What-If parameter

[Anonymous]

I have a table with multiple company data and having company_id column in it. I want to show the slider with the 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 the selected company.

Measures:

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,

What-if Parameter:

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.

-- This is working fine

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

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

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

As you know, the What-If parameter is a kind of calculated table and the calculated table are computed during the database processing , then stored in the model. While the What-If parameter is generating, the value of [Max Value] is always Blank, 

So In your scenario, we cannot make the What-If Parameter dynamically. Even you use the following formula to avoid Max Value be blank, the table of TopRank will be also constant from 1 to DISTINCTCOUNT ( SOURCE_TABLE[Company_Name] ).

Max Value =
VAR result =
    CALCULATE (
        DISTINCTCOUNT ( SOURCE_TABLE[Company_Name] ),
        FILTER (
            SOURCE_TABLE,
            SOURCE_TABLE[Company_id] = SELECTEDVALUE ( SOURCE_TABLE[Company_id] )
        )
    )
RETURN
    IF ( result = BLANK (), DISTINCTCOUNT ( SOURCE_TABLE[Company_Name] ), result )

But if you are using the List Slicer, we can use a measure in Visual Filter as a workaround

FilterMax =
IF ( SELECTEDVALUE ( TopRank[Value] ) <= [Max Value], 1, -1 )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Is the first variable returns blank in any case?

Then, in that case, use if(_var =blank(),1,_var)