Dax measure parent/child

[joki]

Hi

I have a table with customer and one with orders, which is related. 

I my report, I view all customers having orders where some criteria is set for the order.

I need to add a column where I count all the orders (not filtered) for each customer.

Thanks in advance

[v-cgao-msft · Accepted solution]

Hi @joki ,

If you want a measure. Please try this.

Measure = COUNTROWS('core_crm RecurringCharge')

If you want a calculated column. Because some functions cannot be used under direct query, you need to convert the storage method of the table first. Note that this operation is irreversible, you can consider whether to use it according to the situation.

Please create this calculated column in table 'core_crm Contact'.

Column = CALCULATE(COUNTROWS('core_crm RecurringCharge'))

Attaching the PBIX file for reference, hope this helps.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[joki]

I have now changes to "Import" (not direct query) 

I try to create the meassure in "modelling data" but get the same error "Can't find 'core_crm Contact'[ContactId]

RecurringChargeTotalQty = Calculate(COUNTROWS('core_crm RecurringCharge'),

Filter(ALL('core_crm RecurringCharge'),

'core_crm Contact'[ContactId] = 'core_crm RecurringCharge'[Contact]))

[joki]

Thanks I did someting like that before. I get the error "Can't fint customertable[customer]" (customer)

[philouduv]

customertable[customer] must be renamed depending the name of your data.

It is TABLE[column_name] so in your case it would be the dimension table and the column with every customer name appearing only once.

Best regards,

[joki]

I know I have done that

[philouduv]

If you are calculating this column in your DIMENSION table which is customertable it should work.
Otherwise share your data model because I probably miss something.

Best regards,

[joki]

Hi 

Please see below. The issue is between "Contact" and "RecurringCharge" 

I just wrote "Customer" and "Order" to simplify

[joki]

Then I can't use direct query right ?

[philouduv]

Sorry, I did not understand that yo uwere using direct query.

Yes you can but before creating the column click on the Fields panel (with every column measure etc) and select the right table (here customertable).

If it is not working I do not have an answer anymore 😞

Best regards,

PS: i looked on the microsoft documentation and created column with direct queries can not refer to multiple table....
Using DirectQuery in Power BI - Power BI | Microsoft Docs
Part modeling limitation

[philouduv]

It looks like you are creating your column in the wrong place since when you create a column in the display screen it does not go always right. 
Go on modelling data :

then select the right table and here create a column.

Tell me if it works

Best regards,

[philouduv]

Hey @joki,
It would be something like :
Count_order_by_custo = Calculate(COUNTROWS(ordertable),
Filter(ALL(ordertable),
customertable[customer] == ordertable[customer]))

This column must be created in your DIMENSION table (customer)

Best regards,