cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Compare 2 lists

HI All

Need your help and will try and explain as best as possible

I am trying to write a measure that will compare 2 tables and return the values that don't match

Table 1 is the dimension Table - named Fleet which is a fleet of cars numbered 1 to 10 for example

Table 2 is the fact table named cars cleaned

Let's say cars 1,2,3,4,5 were cleaned I want the measure to return 6,7,8,9,10 which weren't cleaned.

both tables are linked in the data model but I still cant write the mesure, do I need to approach the problem as a calculated instead

Thank you

3 REPLIES 3
Frequent Visitor

I am a newbie and I am trying to grasp the language

The calculated column formula is what I am struggling to formulate

can you help me with the formula I was thinking of returning a True or False on the Fleet table if it matched or didn't match with the cars cleaned table then matching the Falses in a measure.

I thought     Buses not cleaned = Fleet[car]=Carscleaned[car] would work but it doesn't

Thank you

Employee

You can try an expression like the one below.  It will give you a list of all the not cleaned cars.  You could also count them with COUNTROWS(__notcleaned), if needed in the Return.

``````Not Cleaned =
VAR __fleetcarsinscope =
VALUES ( Fleet[CarID] )
VAR __cleanedcarsinscope =
VALUES ( Fact[CarID] )
VAR __notcleaned =
EXCEPT ( __fleetcarsinscope, __cleanedcarsinscope )
RETURN
CONCATENATEX ( __notcleaned, [CarID], ", " )``````

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Super User

Ask youself: is the fact that the car has been cleaned impacted by any other filter selection? From how I understand your explanation that would be a no. In such a case a calculated column is sufficient to describe the car's "cleaned" property, as the fact (car having been cleaned) will not change dynamically.

After that you can create measures that enumerate the cleaned cars versus the non-cleaned cars, but now you can do that in the filter context (let's say by car brand, or regardless of brand). Use a "NOT IN {}"  or "FALSE()" filter for your result. For example:

CarsNotCleaned = Calculatetable([Car ID], filter (All(cars),[cleaned]= FALSE()))

Note that this returns a table.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors