Forum Discussion

Ion_Ander's avatar
Ion_Ander
Helper I
4 years ago

two USERELATIONSHIP in same dax formula

Hi,

First i try to explain what i have:
i have a suppliers table(proveedores), that is related to an invoices table(ALBARANES PROVEEDOR), which is related to an invoice details table (LINEAS ALBARANES PROVEEDOR), and that is related to a products table(ARTICULOS).

 

 

The question is that now i need to obtain the purchases that we have made of products from the selected supplier (by vat number) made to another supplier (say vat number 1).

For this i have created another inactive relationship between products and supplier (all products belong to a supplier and in the products table it is specified to which supplier they belong), then i manage to obtain all the purchases made of products from the selected supplier, with the userelationship function.

 

 

 

The problem is: i do not know how to show exclusively purchases made to the supplier with vat 1 number of the products . I think perhaps i will need a second USERELATIONSHIP, but if i try to put it get no result.

 

The formula i try:

 

MyFormula =
VAR SEL =
    SELECTEDVALUE ( PROVEEDOR[NIF] )
VAR RESULT =
    CALCULATE (
        SUM ( 'LINEAS ALBARAN PROVEEDOR'[IMPORTELIMPIO] ),
        ALL ( PROVEEDOR ),
        USERELATIONSHIP ( ARTICULOS[CLAVE PROVEEDOR], PROVEEDOR[clave_PROV] ),
        PROVEEDOR[NIF] = SEL
    )
RETURN
    RESULT

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Ion_Ander , does that mean product vat1 in not sold by any other supplier in the selected duration ? Only supplier one sold it ?

    what all is selected/filtered 

     

     

    example product with only one supplier

    countx(filter(Invoicedetails, Product[Product],"_1" ,distinctcount(Invoicedetails[Supplier_id]), [_1] =1), [Product])

     

    we need to use all(Supplier) if supplier is selected

    • Ion_Ander's avatar
      Ion_Ander
      Helper I
      4 years ago

      Thanks for answering, amitchandak 

       

      No, vat1 is not a product, vat is the  acronym of Value Added Tax or identification fiscal number for a supplier.

       

      What i need is show me the amount of products buyed to a provider 1 (with VAT number 1--in my case VAT=NIF),but  only the products that belong to the selected supplier.

      For Example If i select NIF : "A08002883" i need to show me the amount of products buyed to provider with NIF "1" and the product belong to provider "A08002883". The provider who i buy the products (NIF="1") is a constant, and the provider of the product is variable(the selected one).