Filter records from Power BI Summarize table

[Anonymous]

Hi,

I have create a below table in Power BI. I need to get max region value for each Id.

Below is the DAX I am using to cretae a table in Power BI:

I need to add one more clause which should return max region for each Id.

In case of Id 2 there are two rows, I need to get only one row with Max region value. So the output should be:

Id	Region
1	APAC
2	EMEA
3	NAMER

Can anyone please help me on that?

Thanks!

[mahoneypat · Accepted solution]

Not sure how you are calculating your "max" but here is a table expression that gets your desired result from your example table.  This one determines max alphabetically, but you can substitute in another expression inside the CALCULATE if that is not correct.

Main Summary =
ADDCOLUMNS (
    SUMMARIZE ( Main, Main[Id] ),
    "Region", CALCULATE ( MAX ( Main[Region] ) )
)

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

Regards,

Pat

[amitchandak]

@Anonymous , if the basis of Max names, why Rank then?

Try like

summarize(Table, Table[id], "Region",max(Table[Region]))

[Anonymous · Accepted solution]

Hi @mahoneypat , @amitchandak ,

Thank you so much for your reply!

I tried the below expression and it works perfect.

Emp_Count_Region = SUMMARIZE(FILTER(SUMMARIZE(Main , Main[Id], Main[Region] ),

RANKX(

FILTER(SUMMARIZE(Main , Main[Id], Main[Region]), Main[Id]=EARLIER(Main[Id])

),

CALCULATE(count('Main'[Region]), ALLEXCEPT(Main, Main[Id], Main[Region])) , , DESC

) =1

), Main[Id], "Region1", calculate(max(Main[Region])))

Thanks!

Rinku

[mahoneypat · Accepted solution]

Not sure how you are calculating your "max" but here is a table expression that gets your desired result from your example table.  This one determines max alphabetically, but you can substitute in another expression inside the CALCULATE if that is not correct.

Main Summary =
ADDCOLUMNS (
    SUMMARIZE ( Main, Main[Id] ),
    "Region", CALCULATE ( MAX ( Main[Region] ) )
)

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

Regards,

Pat

[marceloVVR]

Try using the DISTINCT function. After SUMMARIZE, DISTINCT (Main (ID).

maybe work

[Anonymous]

Hi @marceloVVR ,

Thanks for the reply!

I need to select max region for each id. Using distinct it would be difficult to achieve the desire record.

Thanks,

Rinku