[PLEASE HELP!] Measure doesn’t work with specific table.

[tiago]

Hi guys,

I am not an expert on DAX but I am getting better. All my tables are properly related and the primary key is the field PRODUTO.

When I try to add a calculated field from a specific table to this report it returns me many lines, which is wrong. It's like the primary relation between the tables breaks. All lines have same information but different wrong photos.

Thats the measure I've added

Preço Original = CALCULATE(
SUM(PRODUTOS_PRECOS[PRECO1]);FILTER(PRODUTOS_PRECOS;VALUE(PRODUTOS_PRECOS[CODIGO_TAB_PRECO]) = 1))

If I remove the picture the measure works. The realation with the table that has the picture is also working because I can drag any field there appart from this calculated measures. 

Can someone help?

[Anonymous]

Can you show us your relationships?

[tiago]

Sure

Both tables are realted to a table called PRODUTOS (PRODUCTS).

The have a realation of many to 1

On the report I correcly dragged from the Table PRODUTOS the PRODUCT there so it would work as primary key. I think the problem is on the measure it self. Becuase If I drag someother field from the same table that the measure is in, it works...

Sorry that the interface is in portuguese. If you need any extra details let me know.

Regards

[Anonymous]

I'm not a DAX expert either but I'll make a try.

First of all I suggest to put your dimensions (lookup) table on top and facts on the bottom. So in terms of 1-*, the tables with the 1 side of the relationship on top and the * on the bottom. It would be far easier to understand.

If I get your measure correctly, you want to calculate the sum of products in the Product_prices, where the codigo_tab_preco is 1.

However there's a 1-* relationship so you correctly used an Iterator. As long as you're within those two tables (the filter context is using only those two tables) it works because you have 1 line in PRODUCT and you sum the values in PRICE, getting one row only.
BUt as soon as you add the image something breaks because there are MANY images for each product. So Powerbi creates a context that is made of each Price against each Image and this generate several combination of price/image, thus it SUMS against all possible combinations.

The fact is that you have many images for a product and you've to decide which one. If i read correctly you've already chosen "First Foto" in the pivot. So intuitively should work fine, but I'm quite sure that in this case FIRST a filter context is created for your measure and THEN first foto.

As a tentative, I would bring in the PRODUCTS table a calculated column with the first image.

This post should explain 

https://community.powerbi.com/t5/Desktop/Geting-first-value-from-one-to-many-related-table-based-on/...

so your calculated column should be

FirstPicture= calculate(MINA('PRODUTOS_FOTO[Primera Foto]);RELATEDTABLE('PRODUTOS_FOTO'))

(not sure if this works...test it)

Now that you have your picture in the PRODUTOS table, use that one in the pivot, as it will be just one row.