Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors