Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter records from Power BI Summarize table

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 ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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