Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gsamaral77
Helper I
Helper I

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)

mrs.jpg

TAB2 (entry orders)

IW47.jpg

1 ACCEPTED SOLUTION

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.

4.png

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. 

View solution in original post

14 REPLIES 14
gsamaral77
Helper I
Helper I

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)

mrs.jpg

 

TAB2 (apontamento)

IW47.jpg

@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.

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?

v-rzhou-msft
Community Support
Community Support

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:

1.png

TAB2:

2.png

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:

3.png

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. 

 

 

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 🤔

 

Captura de tela 2021-06-16 112202.jpgCaptura de tela 2021-06-16 112218.jpg

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. 

 

 

Hi, thanks for keep trying help me kkk

 

In this dashboard the ideia is not look for the orders separately, but the amount that was perfomed by the scheduled workers in the scheduled date

 

With this measure only work in a table with column order and this measure but if I put in a Card for show the total just appear "in blank", only if I selected some order than count, but the users in this dashboard won't filter by the order, will filter by the date, workers etc

 

Do you know if is that possible? I new in DAX, don't know much about it rs

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.

4.png

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. 

That worked!! Thank you very much, I was trying to do that at least 3 weeks kkk You helped me a lot!!

amitchandak
Super User
Super User

@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é])))

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?

amitchandak
Super User
Super User

@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

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?

And how I can do that?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.