Forum Discussion

BieBel's avatar
BieBel
Helper I
11 months ago
Solved

Measure counting rows, grouped by two different colums

Hi, I have al table with order information. I can easily create a visual showing me the number of ORDER_ID by SALES_REGION and by MONTH_OF_ORDER_DATE But I need to create a measure that gives me th...
Need Help
  • danextian's avatar
    danextian
    11 months ago

    Hi BieBel 

     

    DISTINCTCOUNT ('table'[ORDER ID] should be okay if the ID doesn't repeat in other region or months.  However, if the same ORDER ID can be re-used, try

    COUNTROWS (  
    SUMMARIZE (
        -- Create a table of distinct combinations of the following columns:
        'table'[ORDER ID],           -- Unique order identifier
        'table'[SALES_REGION],       -- Region where the order was made
        'table'[MONTH_OF_ORDER_DATE] -- Month of the order date
    )
)

     

rohit1991's avatar
rohit1991
Super User
11 months ago

Hi BieBel 

 

You can handle this easily with a measure. Just write:

Orders Count = DISTINCTCOUNT ( 'Orders'[ORDER_ID] )

This way you’ll always get the unique count of orders. When you drop SALES_REGION and MONTH_OF_ORDER_DATE into your visual, Power BI automatically applies the filter context so the measure shows the right number for each region and month. If every order ID is unique in your table you could also use COUNTROWS ( 'Orders' ), but using DISTINCTCOUNT is safer in case an order appears more than once. The good part is you don’t need to manually group anything—the visual and context do the grouping for you, so the measure stays simple and works in all scenarios.

BieBel's avatar
BieBel
Helper I
11 months ago

Thanks, but I do not want to drop this measure in a visual. I need it for further calculation(s). danextian's solutions works for me.