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
marceloVVR's avatar
marceloVVR
Icon for Helper I rankHelper I
6 years ago

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

maybe work

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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