Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joki
Regular Visitor

Dax measure parent/child

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @joki ,

 

If you want a measure. Please try this.

Measure = COUNTROWS('core_crm RecurringCharge')

vcgaomsft_1-1651828752900.png

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.

vcgaomsft_2-1651829250732.png

vcgaomsft_3-1651829351018.png

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

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

vcgaomsft_4-1651829759628.png

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

View solution in original post

11 REPLIES 11
v-cgao-msft
Community Support
Community Support

Hi @joki ,

 

If you want a measure. Please try this.

Measure = COUNTROWS('core_crm RecurringCharge')

vcgaomsft_1-1651828752900.png

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.

vcgaomsft_2-1651829250732.png

vcgaomsft_3-1651829351018.png

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

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

vcgaomsft_4-1651829759628.png

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
Regular Visitor

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
Regular Visitor

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

 

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,

I know I have done that

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,

 

Hi 

 

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

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

 

 

joki_0-1651563377433.png

joki_1-1651563419554.png

 

joki
Regular Visitor

Then I can't use direct query right ?

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

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 :

philouduv_0-1651563848188.png


then select the right table and here create a column.

Tell me if it works

Best regards,

philouduv
Resolver III
Resolver III

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors