Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
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.
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)
@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?
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.
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 🤔
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.
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!!
@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?
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |