topic Re: Status rows = (ex) Approved, Approved, Rejected, Approved -------> Measure Returns "Rejected" in DAX Commands and Tips

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

maibacherstr — Wed, 24 Nov 2021 15:12:29 GMT

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

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

smpa01 — Wed, 24 Nov 2021 15:31:46 GMT

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

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

smpa01 — Thu, 25 Nov 2021 14:07:38 GMT

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

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

maibacherstr — Thu, 25 Nov 2021 14:44:34 GMT

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

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

smpa01 — Thu, 25 Nov 2021 15:02:07 GMT

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?

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

maibacherstr — Fri, 26 Nov 2021 16:15:43 GMT

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

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

smpa01 — Fri, 26 Nov 2021 17:32:06 GMT

maibacherstr

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

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

maibacherstr — Fri, 26 Nov 2021 17:45:37 GMT

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

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

kenqui — Fri, 06 Sep 2024 10:53:20 GMT

Hello, BI experts

I am new in BI & DAX and I am learning this new application.

Can you help me in this matter. Thanks.

How to write the formula if I want to show this table?