Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Largest value in a category

Hi

I am strugling with a problem and hope you can help.

 

I need a DAX measure/filter to find the latest quarter for a given category and subcategory. However, my quarter data is text, not a date field.

 

As an example, for Project 1, Main it it would would report PQ7 and for Project 1 Support it would report PQ5. For Project 2 Main it would report PQ12 and for Project 2 Support it would report PQ7

 

CategorySubcategory
QuarterLatestQ
Project 1MainPQ1No
Project 1SupportPQ2No
Project 1SupportPQ5No
Project 1MainPQ4No
Project 1MainPQ7Yes
Project 2SupportPQ4No
Project 2SupportPQ6No
Project 2SupportPQ7No
Project 2MainPQ12Yes
Project 2MainPQ9No

 

I've tried some of the other solutions, including https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Largest-value-from-column/m-p/1641867 , but I'm stuck with needing to filter on category and subcategory

Would appreciate any advice!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    First add a calculated column into the table.

    Quarter No. = VALUE(RIGHT([Quarter], LEN([Quarter])-2))

     

    Then create a measure to get the latest quarter value.

    Latest Quarter = "PQ" & CALCULATE(MAX('Table'[Quarter No.]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]))

    or

    Latest Quarter 2 = 
VAR latestQtr = CALCULATE(MAX('Table'[Quarter No.]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]))
RETURN
CALCULATE(MAX('Table'[Quarter]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]),'Table'[Quarter No.]=latestQtr)

     

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    First add a calculated column into the table.

    Quarter No. = VALUE(RIGHT([Quarter], LEN([Quarter])-2))

     

    Then create a measure to get the latest quarter value.

    Latest Quarter = "PQ" & CALCULATE(MAX('Table'[Quarter No.]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]))

    or

    Latest Quarter 2 = 
VAR latestQtr = CALCULATE(MAX('Table'[Quarter No.]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]))
RETURN
CALCULATE(MAX('Table'[Quarter]),ALLEXCEPT('Table','Table'[Category],'Table'[Subcategory]),'Table'[Quarter No.]=latestQtr)

     

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

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

    Anonymous , You need to create a number from that

     

    a new column

    Quarter no = right([Quarter], len([Quarter]) -2)

     

    Then have column like

    if([Quarter no] = maxx(filter(Table, [category] = earlier([category])),[Quarter no]), "Yes", "No")