Compare columns from two different tables

Hi folks, I hope everyone is fine (and I appologize if I commited some mistake writing in english kkk)

I want to count lines comparing two columns from two differents tables, something like

IF(COUNT(TAB2[Ordem]), TAB2[Empregado]=TAB1[Nome do recurso], TAB2[Dt. inicio execução]=TAB1[Válido até]

The ideia is to verify if the scheduled orders was performed for the scheduled technician in the scheduled date, but I have no ideia how to do it, because in this tables, 1 order can have multiples tasks with the same number. And in table 2 is entry orders, only the orders with "X" in column "Confirm" are perfomed. Someone can help me, please?

TAB1 (Scheduled orders)

TAB2 (entry orders)

Need Help

Anonymous
5 years ago
Hi gsamaral77

Sure. I know your problem should be that when you add my count measure into card visual it will show blank unless you select an order in table visual.

Try to build a new measure sum as below.

SUM = SUMX(SUMMARIZE(TAB1,TAB1[ID],TAB1[Nome do recurso],TAB1[Válido até]),[Count Ordem])

You can use month, date or other columns in TAB1 to build slicers to filter your measures.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Count by measure.pbix23 KB

14 Replies

Anonymous
Not applicable
5 years ago
Hi gsamaral77

We need to use Max code to locate the current row(text type) in TAB1 in Power BI Measure.

I build a sample to have a test.

TAB1:

TAB2:

Try this measure.

Count Ordem = COUNTX ( FILTER ( ALL(TAB2), TAB2[Ordem] = MAX ( TAB1[ID] ) && TAB2[Empregado] = MAX ( TAB1[Nome do recurso] ) && TAB2[Dt. inicio execução] = MAX ( TAB1[Válido até] ) ), TAB2[Ordem] )

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- gsamaral77
  Helper I
  5 years ago
  Amazing!! That's works!!
  
  I have a question, do you know if it's possible that measure count the amount without filter by the order? I don't know if I explain very well 🤔
  - Anonymous
    Not applicable
    5 years ago
    Hi gsamaral77
    
    If you don't have same Name and Date values in two tables with different orders, you can build this measure without filtering by the order.
    
    For Example:
    
    TAB1
    
    ORDER Nome do recurso Válido até
    
    17117777 KL 2021/01/03
    
    17115723 KD 2021/01/03
    
    TAB2
    
    Ordem Empregado Dt. inicio execução
    
    17115723 KL 2021/01/03
    
    If you have this situation, your measure which doesn't count by order will show wrong result. The correct result should equal = 0, but this measure will return to 1.
    
    Count Ordem = COUNTX ( FILTER ( ALL(TAB2), TAB2[Empregado] = MAX ( TAB1[Nome do recurso] ) && TAB2[Dt. inicio execução] = MAX ( TAB1[Válido até] ) ), TAB2[Ordem] )
    
    Best Regards,
    
    Rico Zhou
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gsamaral77
Helper I
5 years ago
Olá, espero que estejam bem

Estou trabalhando em uma Dashboard de Ordens de Programação

Existe uma tabela com as Ordens Programadas (Tab1), nela contém um ID de cada ordem, data programada, data de validade, responsável entre outros

Além disso, há uma tabela de apontamento (Tab2), em que os técnicos apontam o andamento dessas operações, se foi concluida existe uma coluna para marcarem com "x", caso contrário fica em branco.

O problema é que cada ordem tem várias operações que podem ser executadas por técnicos diferentes e em datas diferentes e não existe um ID que relaciona as operações, apenas as ordens.

Eu preciso criar uma medida para saber se as ordens programadas foram executadas, então que conte as operações na Tab2 que tenham sido executadas na data programada e pelo técnico programado comparando com as respectivas colunas na Tab1

Alguém pode me ajudar por favor?

TAB1 (Programação)

TAB2 (apontamento)
- amitchandak
  Super User
  5 years ago
  gsamaral77 you have to create common dimensions order and date and use that.
  
  Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
  - gsamaral77
    Helper I
    5 years ago
    I did that, but I need a measure to count the scheduled orders that are perfomed for the scheduled worker in the scheduled date, but I don't no how to compare columns from different tables in PowerBI. Can you help me?
amitchandak
Super User
5 years ago
gsamaral77 , you have to bring the other column using one of the 4 ways in other table and compare

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
- gsamaral77
  Helper I
  5 years ago
  And how I can do that?
- gsamaral77
  Helper I
  5 years ago
  I tried use LOOKUPVALUES, but how 1 order can have multiples tasks that brings problems. And I just can create a relationship many to many, is that a problem?
amitchandak
Super User
5 years ago
gsamaral77 , A new column in Tab1

COUNTX(filter(TAB2, TAB2[Empregado]=TAB1[Nome do recurso], TAB2[Dt. inicio execução]=TAB1[Válido até]),TAB2[Ordem])

A new measure with a common Ordernum - https://www.seerinteractive.com/blog/join-many-many-power-bi/
calculate(COUNTX(values(order[Ordem]),calculate(count(TAB2[Ordem]))),filter(TAB2, TAB2[Empregado]=max(TAB1[Nome do recurso]), TAB2[Dt. inicio execução]=max(TAB1[Válido até])))
- gsamaral77
  Helper I
  5 years ago
  PowerBI says that te function filter just accept two parameters then a I use "AND" but em both cases doesn't work 🤔
  
  Why you use the function MAX?