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 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:

 

IdRegion
1APAC
2EMEA
3NAMER

 

Can anyone please help me on that?

 

Thanks!

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

5 Replies

  • 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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

     

    Try like

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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