topic Re: Count orders with specific conditions in DAX Commands and Tips

Count orders with specific conditions

gkarlo — Tue, 12 Mar 2024 17:35:59 GMT

I hope someone may assist me with this...

Currently I have a table of projects that can be Won, Lost or No Realized, the column of this os really exisiting, the problem that I have is that the same project can have No Realized and Won Status in different dates, or Not Realized and Lost Status. However, I would like to know how many orders I have with No Realized Status if the project doesn't have status of Won or Lost previously

example of the table

-----------------------------

Project | Status

A | No Realized

A. | Won

B | No Realized

C. | No Realized

C. | Lost

D. | No Realized

D. | Lost

E. | No Realized

F | No Realized

---------------------------

So the result should be:
won projects: 1

lost projects: 2

No realized projects: 3

Re: Count orders with specific conditions

govindarajan_d — Tue, 12 Mar 2024 18:59:47 GMT

Hi gkarlo,

Create a calculated column like this:

StatusValue = IF ( SampleData[Status] = "No Realized", 1, IF ( SampleData[Status] = "Lost", 2, 3 ) )

And then create 3 measures like this:

No of Won = COUNTROWS ( FILTER ( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), [MaxStatus] = 3 ) )

No of Lost = COUNTROWS ( FILTER ( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), [MaxStatus] = 2 ) )

No of No Realized = COUNTROWS ( FILTER ( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), [MaxStatus] = 1 ) )

The same can be achieved using RANK formula also.

Tested:

Upvote and accept as a solution if it helped!

Re: Count orders with specific conditions

gkarlo — Wed, 13 Mar 2024 08:06:23 GMT

hi Govindarajan,

I tested it, so far it worked very nice... thanks for your support 👍

Re: Count orders with specific conditions

gkarlo — Wed, 13 Mar 2024 10:34:06 GMT

Hi again 👋👋,

if I would like to have the three measures in one column chart, how could I do it? cause in x-axes is not possible on power BI... something like this, I would really apreciate your support 😁

Re: Count orders with specific conditions

govindarajan_d — Wed, 13 Mar 2024 16:28:15 GMT

Hi gkarlo,

Did you try using the stacked bar/column chart?

Re: Count orders with specific conditions

gkarlo — Wed, 13 Mar 2024 17:19:24 GMT

Thanks 🙏... btw I have tried to follow that you did with respect on counting the number of projects but regarding the price but I didn't have any success,

DATASAMPLE TABLE

Project | Status.

A | No Realized.

A. | Won.

B | No Realized.

C. | No Realized.

C. | Lost.

D. | No Realized.

D. | Lost.

E. | No Realized.

F | No Realized.

---------------------------

PROYECTLIST TABLE

Project | Price

A | 100€

A. |100€

B | 50€

C. | 50. €

C. | 50 €

D. |150 €

D. | 150€

E. |50€

F | 50€

So the result should be:
won projects: 100€

lost projects: 200€

No realized projects: 150€

For example only I tried to change the given measure in order to sum all prices with respect on this status:

No of No Realized =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 1
    )
)

Instead of COUNTROW, I put SUMX

and for the table SampleData, I put another table that contains the price (different to the table of projects and status, but those have a linked relationship)

I don't know which is the missing step or in this case there is another way to get the revenue value. Thanks for the given time as well and I hope you may help me 🙏

Re: Count orders with specific conditions

govindarajan_d — Wed, 13 Mar 2024 17:31:30 GMT

Hi gkarlo,

Can you try like this:

No of No Realized = COUNTROWS ( FILTER ( ADDCOLUMNS( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), "SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price]) ), [MaxStatus] = 1 ) )

Re: Count orders with specific conditions

gkarlo — Wed, 13 Mar 2024 17:50:24 GMT

Thanks, I put the code but the measure show only the number of projects but not the sum of prices 😅(as the previous code),

Re: Count orders with specific conditions

govindarajan_d — Wed, 13 Mar 2024 17:54:00 GMT

Hi gkarlo,

Sorry about that. I missed to replace COUNTROWS.

No of No Realized = SUMX ( FILTER ( ADDCOLUMNS( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), "SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price]) ), [MaxStatus] = 1 ), [SumProject] )

Re: Count orders with specific conditions

gkarlo — Wed, 13 Mar 2024 18:03:20 GMT

No worries 😅, thanks too much, it works correctly 🙌

Re: Count orders with specific conditions

govindarajan_d — Thu, 14 Mar 2024 00:41:14 GMT

gkarlo, Glad it worked!