cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gsamaral77
Frequent Visitor

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
Frequent Visitor

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

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

View solution in original post

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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors