Summarized columns

[Anonymous]

Hi Team, I am trying to summarize on multiple columns but always it's throwing same error. I didn't understand the cause.

[v-lili6-msft]

HI  @Anonymous 

For your formula, this returns a table not a value

https://docs.microsoft.com/en-us/dax/topn-function-dax

Returns the top N rows of the specified table.

then for your measure , it should like this:

Ranking 1=

var test =

     TOPN(

50,

SUMMARIZECOLUMNS(

'Provider'[Name],

"Paid_Amount", 'Fact Claim'[Paid Amount],

"Unique_Patient", 'Fact Claim'[Unique Patient],

"Total_Claim_Services", 'Fact Claim'[Total Claim Services],

"Avg_Amount_Paid_by_Service", 'Fact Claim'[Avg Amount Paid by Service]

),

[Paid_Amount],

0,

'Provider'[Name],

1

)

Return

<Your expressiom>

https://www.sqlbi.com/articles/variables-in-dax/

if you still have the problem, please share your sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Hi Please find the sample pbix file and Expected output. Here i want to show Top 50 providers on rank wise while sorting the paid amount.

Note: Here my orginal source is tabular model so i can't create index column so we need to apply dax to show ranks top 50 records , i have already filtered out Top 50 records just i want show one column for Ranks.

Rankx = RANKX(ALL('Fact_Claim_Base_Vw'),[Paid Amount],,DESC,Dense)-- it's showing duplicate records.

Here am using another measure to show one column for ranks

evaluate
ADDCOLUMNS(test, "Rank", countrows(FILTER (TEST,[Paid_Amount]>=EARLIER([PAID_AMOUNT]) )

))

it's working fine in while am writing tabular database level but power bi not working.

https://drive.google.com/drive/folders/1FaeEd-8H0r7MkoIhUG5H50XoRzhT2-80?usp=sharing

[AllisonKennedy]

What are you trying to achieve?

You're using a VAR variable to define a table as a variable, but you're missing a 

RETURN

dax expression here

You need to add the RETURN value, based on what you want the measure to actually do.

[Anonymous · Accepted solution]

By mistake but its not variable. but still it's showing same issue.

[mahoneypat]

Assuming this is just a variable in a longer expression, you are not using SUMMARIZECOLUMNS correctly.  Please see this article - https://www.sqlbi.com/articles/introducing-summarizecolumns/

You are naming your new columns, but you seem to be referencing columns after those (instead of measures).  For example, is 'Fact Claim'[Paid Amount] a column or a measure on that table.  If a measure, consensus is not to list the table with measures (so people that review your code will know it).  If a column, you need to have an aggregation, potentially wrapped in a CALCULATE( ) to get context transition, or just reference an existing measure.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat