Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gérard Ducouret]

Hi,
I tried to use the RELATED function in the formula:

Red Sales = SUMX(FILTER(Sales; RELATED(‘Products'[Color])=”Red”);Sales[OrderQuantity] * Sales[Max Price Paid])

but I get the Error :
The expression specified in the query is not a valid table expression.
What is wrong ?
Thanks

——————————
Gérard Ducouret
——————————

[Nicolas MENDEZ]

Hi Gérard,

For me it is quite strange, beacuse if the Sales table (many side) is properly connected to the products table (1 side) in the model, the formula should be working.

But regarding to the error message, PBI is expecting a TABLE as result and not a scalar as returned by SUMX.

I’m sorry if my question seems to be stupid 🙂 , but may you confirm that the formula is written as a “New Measure” and not as “New table” ?

Otherwise, may you give us some more details about your PBI file ?

Hope it will help…

——————————
Nicolas MENDEZ
Datanaut & Power Bi Addict

France – Belgium – Netherlands – Luxembourg – Switzerland
——————————
——————————————-

[Gérard Ducouret]

Hi Nicolas,
You are right : The formula has been written as “New Table” 🙁
Thanks a lot !

——————————
Gérard Ducouret
——————————
——————————————-

[William Skelley]

Hi Gerard:

In case this helps, I have a couple possible formula alternatives attached. I believe related table(using table name) is used whenever there is a one to many relationship.

Best regards,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[Nicolas MENDEZ]

Hi all,

You’re right Bill, the “Red Sales Alt” is the one to use ..

I would even create 2 measures :

Sales_Amount =  SUMX(Sales, Sales[OrderQuantity] * Sales[Max Price Paid])

Red_Sales_Amount = CALCULATE([Sales_Amount] , FILTER(‘Products’ , ‘Products'[Color]=”Red”))

——————————
Nicolas MENDEZ
Datanaut & Power Bi Addict

France – Belgium – Netherlands – Luxembourg – Switzerland
——————————
——————————————-

[William Skelley]

Hi Nicolas:

Thanks for verifying, I default to you any day on these matters.

Take care,

Bill

——Original Message——

Hi all,

You’re right Bill, the “Red Sales Alt” is the one to use ..

I would even create 2 measures :

Sales_Amount =  SUMX(Sales, Sales[OrderQuantity] * Sales[Max Price Paid])

Red_Sales_Amount = CALCULATE([Sales_Amount] , FILTER(‘Products’ , ‘Products'[Color]=”Red”))

——————————
Nicolas MENDEZ
Datanaut & Power Bi Addict

France – Belgium – Netherlands – Luxembourg – Switzerland
——————————