Comparing Datasets

[Anonymous]

Hi,

I have a dataset similar to the below.

Dataset 1 - Invoice details

Task ID	Work Type	Cost
1	Erect	10
2	Dismantle	30
3	Erect	80

Dataset 2 - Product Table

Job ID	Task ID
34	1
64	2
27	3

Relationships created

Dataset 1 - Dataset 2 (Task ID)

I want to have a two table visuals that highlights Job ID's that meet the below criterias:

1. Jobs that have had an erect invoice but not a dismantle

2. Jobs that have had a dismantle invoice but not an erect

Can anyone help me?

[Anonymous]

Hi @Anonymous ,

Please enable this preview. (The preview feature will take effect after restarting the desktop)

Connect to two datasets and create a relationship.

Then create a measure.

Measure = 
VAR _num_of_not_dismantle = 
CALCULATE(
    COUNTROWS
    (RELATEDTABLE('Invoice details')),
    'Invoice details'[Work Type]<>"Dismantle"
)
VAR _num_of_not_erect = 
CALCULATE
(COUNTROWS(
    RELATEDTABLE('Invoice details'))
    ,'Invoice details'[Work Type]<>"Erect"
)
RETURN
SWITCH(
    TRUE(),
    _num_of_not_dismantle=1,"Green",
    _num_of_not_erect=1,"Yellow")

Set the conditional format for the [Job id] field.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Hi, 

Thank you for your response. I have added these steps into my report and are experiencing some issues.

Yellow is working as intended and showing only 'Task ID's' and 'Job ID's' with "Dismantle" records only in the 'Invoice Details' table.

How ever, green seems to be showing 'Task ID's' and 'Job ID's' with "Erect" & "Dismantle" records instead of the inteded "Erect" records only.

Any ideas as to why this might be happening?

[Anonymous]

Hi @Anonymous ,

Can you provide a file with no private data to cover this question? Thanks in advance.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Gao
Community Support Team

[Anonymous]

I have pulled over the 'Work Type' column from dataset 1 (Invoice details) . This breaks creates duplicate rows where there is a task ID that has an "Erect" & "Dismantle" work type.

Looking at the above image you can see the logic works for dismantle only worktype (yellow). But erect logic is showing some ID's that have had only erect but also ID's with erect & dismantle.

For example, ID 07457 has had an erect and dismantle work type but is still showing as green.

Would it be possible to create a third variable that highlights ID's pink if there is an erect and dismantle worktype?