Forum Discussion

zenisekd's avatar
zenisekd
Super User
2 years ago
Solved

FORMAT function trouble

Hi, I have a situation, where 
I have a simple relation between product and sales order line.


SO line contains subtotal value by order currency(currency sold to customer) and company currency (value in currency of selected company). There are multiple companies with different currencies. 

When I display Order code, product and value I get  this table, which is fine.: 

Now, when I want to see this value formated in company currency I tried to use this dax function:

Order Value =
 format (
        calculate (
            sum ( sale_order_line[Subtotal by Company Currency] ),
            sale_order_line,sale_order_line[state]  IN {"draft", "sent"}
            )
            , "# ##0.0 " & SELECTEDVALUE(sale_order_line[company_currency_symbol])
      )



This is what I get: the product, linked form product table multiplies for some reason (cartesian merge)...

 

After some testing I found out that it is caused by the FORMAT function. Whenever I add FORMAT in any form even using just (

"currency") and there is product column included in the table, it does this. 

Could anyone tell me, why is it happening?
Need Help

3 Replies