Need a static measure that doesn't change across rows

[joglidden2]

Do I need to even describe the problem any further? How is this not a previous post?

Here's the measure: 

__max = max(SITE[Personnel SC Count])

The value is 2047.

In a table, or calculated column, it changes on every row. I want it to be static on the value of 2047. 

How is this done?

[joglidden2]

Thank you everyone for your suggestions. Solving this problem turned out to be far easier, faster, and more reliable in SQL using stored procedures, functions, views, etc.  This is what I would recommend to anyone pursuing this sort of problem with SQL Server as a data source. That said, the accepted solution appears to be workable. 

[v-shex-msft · Accepted solution]

HI @joglidden2,

It sounds like you want to get the specific value from the table without filter effects. For this scenario, I'd like to suggest you add the 'all' function on the table to ignore the filter effect, then you can get the static values.

Measure =
CALCULATE ( MAX ( SITE[Personnel SC Count] ), ALL ( SITE ) )

Reference link of the detailed description of all functions:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

Regards,
Xiaoxin Sheng

[joglidden2]

Thanks everyone for your help and suggestions. Basically, I'm giving up on doing this level of custom development in Power BI. It's just too much trouble, and it's a lot easier doing it in SQL Server and in code (C#, Java). Power BI is great for reporting results, but I now realize that it's more efficient and easier to build the logic elsewhere. 

[Caz_16]

@joglidden2 

You could also use a variable.

___max = 
VAR Max_Personnel = 
MAX(ALL(SITE[Personnel SC Count]))
RETURN

Max_Pesonnel

You can then use that variable for further calculations using DAX after the RETURN character. 

[joglidden2]

Nope. "The MAX function only accepts a column reference as an argument." Good idea though.

[Caz_16]

Im not sure why mine didn't work. SITE[Personnel SC Count] is a column, right? Try removing the "ALL", just pass it your column. Some sample data would help everyone understand the issue a little better. 

I tested this with my own dataset and it works perfectly. It will produce the same number no matter what I table or Viz i apply that measure to.

[mhossain]

@joglidden2 

I believe you are dragging this measure to table and there are are other column, so obviously it is showing max at Other column level.

You can try:

__max = 

 CALCULATE (

                       max(SITE[Personnel SC Count])

                  ,

                      ALL(Other column)

                   )

Please update measure, hope above is clear and it helps.

[joglidden2]

That's right, I'm dragging the measure to a new column in the table. Then the table gives me the measure value for each row, which is not what I want. 

Yes, there are other columns. I tried a different column (in the 'ALL' expression) and got the same result as before. What you suggested didn't work. 

[mhossain]

@joglidden2 

Share the pbix with sample data if possible.

[joglidden2]

@mhossain thanks for your suggestions. I can't provide the report or data because it's sensitive information. I'm giving up on PBI for this type of development. 

[amitchandak]

@joglidden2 , Try like

__max = max(allselected(SITE), SITE[Personnel SC Count])

or

__max = max(all(SITE), SITE[Personnel SC Count])

[joglidden2]

No dice. "A single value for column ... cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."