Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Relate two table without a Key with conditional

Hi guys

Could someone tell me if what I need do is possible? I'm really lose with this problem.

I have two tables, in the table "One" I have one colum of "Volume" where you can find tha quantity of protucts I need to send to my costumers, in the second table I have the the colum "Tipo" that says the amount of products each Truck can transportation, you can see the weight range in colums "Capacidade de" and " Capacidade até", but I have two extra conditional, first is the colum "City" because the trucks can be diferent in each city and "Carrier" for the same reason.

So, based in colum "Volume" from the table one, I need de bring witch kind of truck that the weight range is equivalent considering the City and the carrier. 
Find below on picture of the tables
(I'm sorry about the my bad english)

 

Table on

Table two

 

att

Daniela Xavier

 

Need Help DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Ok. Please try

    Number of Vehicles = 
VAR CurrentVolum = SELECTEDVALUE ( Salles[Volume] )
VAR MAXCapacity = MAX ( Carriers[CAPACIDADE DE] )
RETURN
IF ( 
    NOT ISBLANK( CurrentVolum ),
    ROUNDUP ( DIVIDE ( CurrentVolum, MAXCapacity ), 0 )
)

10 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    There is mutch that be done here. For example optimizing the selection based on cost of transpotation of all available options or based on maximum utilization factor. That would be the challange. However, th easiest part is to retrieve the capcity of the biggest vehicle avialable from the vendor in the same city. This is a sample file with such example https://we.tl/t-CndjIAATxw

    Selected TIPO = 
VAR CurrentVolum = VALUES ( Salles[Volume] )
VAR MAXCapacity = MAX ( Carriers[CAPACIDADE DE] )
VAR MaxType = 
    MAXX ( 
        FILTER ( 
            VALUES ( Carriers[TIPO] ), 
            CALCULATE ( VALUES ( Carriers[CAPACIDADE DE] ) = MAXCapacity ) 
        ),
        Carriers[TIPO] 
    )
RETURN
IF ( 
    NOT ISEMPTY ( CurrentVolum ),
    MaxType
)
    Number of Vehicles = 
VAR CurrentVolum = VALUES ( Salles[Volume] )
VAR MAXCapacity = MAX ( Carriers[CAPACIDADE DE] )
RETURN
IF ( 
    NOT ISEMPTY ( CurrentVolum ),
    ROUNDUP ( DIVIDE ( CurrentVolum, MAXCapacity ), 0 )
)

    Please let me know how can I support you further.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tamerj1 

      Thanks a lot for help me, I downloaded you file and I tried to do exactly similar you, but It didn't work. Can you see any mistake in my picture? Find below the translation of the message.

      Find bellow the translation of the message from the box

      "a table of several values was provided, with a single expected value."

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 
        Ok. Please try

        Number of Vehicles = 
VAR CurrentVolum = SELECTEDVALUE ( Salles[Volume] )
VAR MAXCapacity = MAX ( Carriers[CAPACIDADE DE] )
RETURN
IF ( 
    NOT ISBLANK( CurrentVolum ),
    ROUNDUP ( DIVIDE ( CurrentVolum, MAXCapacity ), 0 )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous , I do not understand what output you want. Can you please say what output you expect for the first row with volumn 203682?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

       

      Of course, I want the formula brings "Carreta" that is the biggest truck, I know afertr that I'll need to divide "Volume"/ "Capacidade até" to find a quantitity of trucks I'll nedd. But for now I need bring the truck first.

      Look the exemple bellow, in this case a need show "Toco". Because Is equivalent at weight range of Carrier "Jadlog" from the City "Campinas"

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous , let me see if I understand the logic.

        For the first example, we have in Table 1 Volume =203682 and City = Barueri.

        So we search Table 2 for rows that have City = Barueri and choose the row with the highest [Capacidade até].

        That row has [Capacidade até] = 14000 and has [Tipo] = Carreta, so Carreta is the desired result.

         

        I will apply the same logic to example 2: we have in Table 1 Volume =1473 and City = Campinas.

        So we search Table 2 for rows that have City = Campinas and choose the row with the highest [Capacidade até].

        That row has [Capacidade até] = 6800 and has [Tipo] = Carreta, so Carreta is the desired result. But you say it should be "Toco".

        I do not understand the logic you are trying to acheive. Can you explain more clearly please with a few more examples?

         

        Edit: OK, I think I understand it better now that you've posted more data.

        You want to find the [Tipo] from Table 2 with the same [City] and where [Volume] is between [Capacidade de] and [Capacidade até]. If there is no row, then pick the [Tipo] from Table 2 with the [City] with the highest [Capacidade até].

        This measure should achieve it:

         

        Calculated Tipo = 
   VAR vCity = SELECTEDVALUE('Table2'[City])
   VAR vVolume = SELECTEDVALUE('Table2'[Volume])
   VAR vTipoInRange = 
    MAXX(
        TOPN(
            1,
            FILTER(
                'Table1',
                'Table1'[City] = vCity
                && vVolume >= 'Table1'[CAPACIDADE_DE]
                && vVolume <= 'Table1'[CAPACIDADE_Ate]
            ),
            'Table1'[CAPACIDADE_DE], ASC
        ),
        'Table1'[TIPO]
    )

    VAR vHighestCapacityTipo = 
        MAXX(
            TOPN(
                1,
                FILTER(
                    'Table1',
                    'Table1'[City] = vCity
                ),
                'Table1'[CAPACIDADE_Ate], DESC
            ),
            'Table1'[TIPO]
        )

    RETURN 
        COALESCE(vTipoInRange, vHighestCapacityTipo)

         

        If the COALESCE function gives an error, then replace

        COALESCE(vTipoInRange, vHighestCapacityTipo)

        with

        IF(ISBLANK(vTipoInRange), vHighestCapacityTipo, vTipoInRange)

         

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    would you please share a copy/paste sample data?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tamerj1 

       

      Yes, Here is it

      Sorry past it without formatting, but the community did allowed past the table

      carrier table 1

      City TIPO Carrier CAPACIDADE DE CAPACIDADE ATÉ
      BARUERI FIORINO JADLOG 1 210
      BARUERI VAN JADLOG 211 750
      BARUERI VUC JADLOG 751 1.500
      BARUERI 3/4 JADLOG 1501 2.000
      BARUERI TOCO JADLOG 2001 3.000
      BARUERI TRUCK JADLOG 3001 5.000
      BARUERI CARRETA JADLOG 5001 14.000
      CAMPINAS FIORINO JADLOG 1 195
      CAMPINAS VAN JADLOG 196 585
      CAMPINAS VUC JADLOG 586 780
      CAMPINAS 3/4 JADLOG 781 1.200
      CAMPINAS TOCO JADLOG 1201 2.000
      CAMPINAS TRUCK JADLOG 2001 3.000
      CAMPINAS CARRETA JADLOG 3001 5.200
      CAMPINAS FIORINO SEQUOIA 1 190
      CAMPINAS VAN SEQUOIA 191 570
      CAMPINAS VUC SEQUOIA 571 760
      CAMPINAS 3/4 SEQUOIA 761 1.140
      CAMPINAS TOCO SEQUOIA 1141 1.900
      CAMPINAS TRUCK SEQUOIA 1901 3.600
      CAMPINAS CARRETA SEQUOIA 3601 5.800
      RIO DE JANEIRO FIORINO JADLOG 1 210
      RIO DE JANEIRO VAN JADLOG 211 750
      RIO DE JANEIRO VUC JADLOG 751 1.500
      RIO DE JANEIRO 3/4 JADLOG 1501 2.000
      RIO DE JANEIRO TOCO JADLOG 2001 3.000

       

      Table two - salles

      Data Carrier City Volume
      08/07/2022 Sequoia BARUERI 54241
      12/07/2022 Sequoia BARUERI 48878,5
      13/07/2022 Sequoia BARUERI 44610
      11/07/2022 Sequoia BARUERI 43092
      08/07/2022 Jadlog BARUERI 38771,5
      12/07/2022 Jadlog BARUERI 34879,5
      14/07/2022 Sequoia BARUERI 33927
      13/07/2022 Jadlog BARUERI 32173
      11/07/2022 Jadlog BARUERI 30597,5
      09/07/2022 Sequoia BARUERI 29130
      14/07/2022 Jadlog BARUERI 24706,5
      15/07/2022 Sequoia BARUERI 24013
      19/07/2022 Sequoia BARUERI 23937
      07/07/2022 Sequoia BARUERI 22917,5
      26/07/2022 Sequoia BARUERI 22841,5
      27/07/2022 Sequoia BARUERI 20938
      09/07/2022 Jadlog BARUERI 20722
      20/07/2022 Sequoia BARUERI 20303,5
      29/07/2022 Sequoia BARUERI 19669
      28/07/2022 Sequoia BARUERI 19669
      21/07/2022 Sequoia BARUERI 19213,5
      06/07/2022 Sequoia BARUERI 18774
      15/07/2022 Jadlog BARUERI 17604,5
      19/07/2022 Jadlog BARUERI 17482
      26/07/2022 Jadlog BARUERI 16859
      07/07/2022 Jadlog BARUERI 16793,5
      22/07/2022 Sequoia BARUERI 16669
      27/07/2022 Jadlog BARUERI 15454,5
      05/07/2022 Sequoia BARUERI 15314
      20/07/2022 Jadlog BARUERI 14986
      18/07/2022 Sequoia BARUERI 14897,5
      29/07/2022 Jadlog BARUERI 14517,5
      28/07/2022 Jadlog BARUERI 14517,5
      01/07/2022 Sequoia BARUERI 14224,5
      21/07/2022 Jadlog BARUERI 14181,5
      25/07/2022 Sequoia BARUERI 13958,5
      06/07/2022 Jadlog BARUERI 13810,5
      22/07/2022 Jadlog BARUERI 12303
      04/07/2022 Sequoia BARUERI 11846,5
      16/07/2022 Sequoia BARUERI 11679,5
      05/07/2022 Jadlog BARUERI 11168
      18/07/2022 Jadlog BARUERI 10748
      01/07/2022 Jadlog BARUERI 10458,5
      25/07/2022 Jadlog BARUERI 10303
      16/07/2022 Jadlog BARUERI 8562,5
      04/07/2022 Jadlog BARUERI 8539,5
      08/07/2022 Jadlog CAMPINAS 7364,5
      12/07/2022 Jadlog CAMPINAS 5837,5
      11/07/2022 Jadlog CAMPINAS 5678,5
      08/07/2022 Sequoia CAMPINAS 5101
      13/07/2022 Jadlog CAMPINAS 4245,5
      07/07/2022 Jadlog CAMPINAS 4104,5
      12/07/2022 Sequoia CAMPINAS 4069
      11/07/2022 Sequoia CAMPINAS 3990,5
      14/07/2022 Jadlog CAMPINAS 3501,5
      15/07/2022 Jadlog CAMPINAS 3457
      18/07/2022 Jadlog CAMPINAS 3396
      19/07/2022 Jadlog CAMPINAS 3368,5
      20/07/2022 Jadlog CAMPINAS 3344,5
      21/07/2022 Jadlog CAMPINAS 3325
      22/07/2022 Jadlog CAMPINAS 3309
      23/07/2022 Jadlog CAMPINAS 3309
      25/07/2022 Jadlog CAMPINAS 3280,5
      26/07/2022 Jadlog CAMPINAS 3265,5
      27/07/2022 Jadlog CAMPINAS 3254
      28/07/2022 Jadlog CAMPINAS 3245,5
      29/07/2022 Jadlog CAMPINAS 3239
      06/07/2022 Jadlog CAMPINAS 3207
      05/07/2022 Jadlog CAMPINAS 3205,5
      01/07/2022 Jadlog CAMPINAS 3199
      30/07/2022 Sequoia BARUERI 2963,5
      13/07/2022 Sequoia CAMPINAS 2932
      04/07/2022 Jadlog CAMPINAS 2898
      07/07/2022 Sequoia CAMPINAS 2761,5
      14/07/2022 Sequoia CAMPINAS 2403,5
      15/07/2022 Sequoia CAMPINAS 2362
      18/07/2022 Sequoia CAMPINAS 2305,5
      19/07/2022 Sequoia CAMPINAS 2282
      20/07/2022 Sequoia CAMPINAS 2263
      21/07/2022 Sequoia CAMPINAS 2247,5
      23/07/2022 Sequoia CAMPINAS 2234,5
      22/07/2022 Sequoia CAMPINAS 2234,5
      25/07/2022 Sequoia CAMPINAS 2212
      26/07/2022 Sequoia CAMPINAS 2200
      27/07/2022 Sequoia CAMPINAS 2191