topic Re: Dax measure parent/child in DAX Commands and Tips

Dax measure parent/child

joki — Tue, 03 May 2022 06:54:02 GMT

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

Re: Dax measure parent/child

philouduv — Tue, 03 May 2022 06:57:51 GMT

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,

Re: Dax measure parent/child

joki — Tue, 03 May 2022 07:22:31 GMT

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

Re: Dax measure parent/child

philouduv — Tue, 03 May 2022 07:25:57 GMT

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,

Re: Dax measure parent/child

joki — Tue, 03 May 2022 07:28:44 GMT

I know I have done that

Re: Dax measure parent/child

philouduv — Tue, 03 May 2022 07:31:57 GMT

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,

Re: Dax measure parent/child

joki — Tue, 03 May 2022 07:38:22 GMT

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

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

Re: Dax measure parent/child

philouduv — Tue, 03 May 2022 07:46:26 GMT

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,

Re: Dax measure parent/child

joki — Tue, 03 May 2022 07:48:18 GMT

Then I can't use direct query right ?

Re: Dax measure parent/child

philouduv — Tue, 03 May 2022 07:57:53 GMT

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

Re: Dax measure parent/child

joki — Tue, 03 May 2022 09:07:12 GMT

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]))

Re: Dax measure parent/child

Anonymous — Fri, 06 May 2022 09:38:23 GMT

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