Measure counting rows, grouped by two different colums

[BieBel]

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 this number of ORDER_ID by SALES_REGION and by MONTH_OF_ORDER_DATE because I have to use this in further calculations (productivity etc.)

And I cannot get it right. What would be the correct DAX formula?

ORDER_ID

ORDER_DATE

MONTH_OF_ORDER_DATE

AMOUNT_NET

VAT

SALES_REGION

[v-pnaroju-msft]

Thankyou, @rohit1991 and @danextian for your responses.

Hi BieBel,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on our understanding of the scenario, we have attached a screenshot and a sample PBIX file that may help resolve the issue:

We hope the information provided is helpful. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[danextian · Accepted solution]

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]

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]

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.