Forum Discussion

Brendy_P's avatar
Brendy_P
Helper I
6 years ago

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

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.

  • Brendy_P's avatar
    Brendy_P
    Helper I
    6 years ago

    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