Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Brendy_P
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 

Please help

Thank you

3 REPLIES 3
Brendy_P
Frequent Visitor

Thank you for your reply

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

mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


lbendlin
Super User
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.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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