Status rows = (ex) Approved, Approved, Rejected, Approved -------> Measure Returns "Rejected"

[maibacherstr]

Hello BI Community, thanks in advance.

I'm working with two columns, ID and Status. As you can see below, the ID column may have duplicate ID #s. For a given ID, it will have a status of either "Approved" or "Rejected" (or null), but the challenge to write in DAX is to be able to identify ANY instance of a "Rejected" status for a given ID. For example, if there are 10 rows where ID= 50, 8 are "Accepted," 1 is null, and 1 is "Rejected," I need to return "Rejected."

I don't do this very often. Can anyone please help? Thanks again,

Dan

[maibacherstr]

Yes I did, thank you very much for your reply. The solution isn't completely integrated yet though. On first attempt I can get the measure working, but with an error that I think is based on a table relationship as follows:

We've been working with my "approve/reject" table which is a many-to-one relationship with my "packages" table. (The rule I'm working with is that each package goes through an approval process and if any of the approvers rejects the package, then the "packages" table will reflect it).

One first attempt, dropping the new measure into my Packages table visual results in every row showing "rejected." On the other hand, dropping the new measure into a "reject/approve" table visual works like a charm.

Dropping related fields into a table visual has always been thorny to me. Seeing your expertise so far I'd love to hear any more of your thoughts. Thanks again,

Dan

[smpa01]

@maibacherstr  you did not fully mention in the original psit about the data model. I thought everything was coming from the same table.

Can you please post the sample pbix and show the desired output?

[maibacherstr]

Hello @smpa01 , thanks again. I modified the pbix that you posted, built the data model, and added a Details tab showing the same as the graphic below. To restate the goal (as shown in the gray box in the graphic):

GOAL is to drop the Measure into the PackageTable and produce the result shown in the "Should Show As" column

(As shown in this example, the PackageTable.Status column is a manual entry)

RECAP: 1st attempt resulted in all "Measure" rows showing as "Rejected"

Side note but I'm trying to attach a pbix to the community thread for the first time and am surprised this interface isn't making it easy at all, I'm not sure the best method but I think this should work.

Thanks again

[smpa01 · Accepted solution]

@maibacherstr 

Measure 2 = 
IF (
    CALCULATE (
        CALCULATE ( COUNT ( 'Approval Table'[Status] ), 'Approval Table'[Status] = "Rejected" ),
        ALL('Package Table'),VALUES('Package Table'[PackageID])
    ) >= 1,
    "Rejected",
    "Approved"
)

[maibacherstr]

It worked perfectly! So the difference at the end came down to the "all" vs "allexcept" functions by the look of it.

You are amazing and I hope you have a wonderful day.

Dan

[smpa01]

@maibacherstr  you can write a measure like this

Measure =
IF (
    CALCULATE (
        CALCULATE ( COUNT ( 'Table'[Status] ), 'Table'[Status] = "Rejected" ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    ) >= 1,
    "Rejected",
    "Accepted"
)

pbix is atatched

[smpa01]

@maibacherstr  did you have a chance to look into the solution I provided?