cancel
Showing results for
Did you mean:
Regular Visitor

Countx with some conditions

Hello!!

Im trying to do this in a calculated column in dax, im not sure where is the mistake.

I have a table with information like this:

I want to count by country, supplier and mat_group; im trying with this dax expresion, for test the supplier and the mat_group, but I dont have the result that I expect.

Count = COUNTX(FILTER('Mytable','Mytable'[SUPPLIER]=EARLIER('Mytable'[SUPPLIER]) && 'Mytable'[MAT_GROUP]=EARLIER('Mytable'[MAT_GROUP])),'Mytable'[MAT_GROUP])

I actually received the count of the actual count, but I expected the column expect count.

I hope someone could help me!

Greetings

Mónica

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Countx with some conditions

@MonicaElizondo Please try below using "New Column"

`ActualCount = CALCULATE(COUNTROWS(MatGroups),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))`
`ExpectCount = CALCULATE(DISTINCTCOUNT(MatGroups[MatGroup]),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))`

Note - You can solve the same in "Power Query" as well using "GROUP BY" option..

Hope this helps !!

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

6 REPLIES 6
Member

Re: Countx with some conditions

This is a pretty simple calculated column, especially if you only have COUNTRY, SUPPLIER, and MAT_GROUP columns:

`Count = CALCULATE(COUNTROWS(Mytable))`

If you do have other columns, then use this formula:

`Count = CALCULATE(COUNTROWS(Mytable),ALLEXCEPT(Mytable,Mytable[COUNTRY],Mytable[SUPPLIER],Mytable[MAT_GROUP])) `

I'll also note that you can get this in the report layer with a few drag and drops - click the table visual, drag in all columns, and then drag in a duplicate column and set to count:

Regular Visitor

Re: Countx with some conditions

Maybe I dont can explain me, Im looking when I filter country and supplier show me the count of diferentes mat_groups, for example

Country   Supplier       Mat_group

US               A                  123

US               A                  123

US               A                  456

US               B                   123

When I filter US and Supplier A, I want to see in count column 2 (the two diferent mat_group) and when y filter supplier B i want to see count 1 (mat_group 123)

I have more columns in the table, almost 150

I dont use the solution in the report layer, because with the information im going to do another calculations before show in the report layer.

Thanks again

Monica

Super User

Re: Countx with some conditions

@MonicaElizondo Please create a "New Measure" as below:

`CntMsrGrps = DISTINCTCOUNT(MatGroups[MatGroup])`

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Highlighted
Regular Visitor

Re: Countx with some conditions

@PattemManohar thanks a lot for you reply.

Is possible to do this in a column? I want to have the value in a column because I need for another calcutations.

I tried this solution in a column but it doesnt work that I want.

Thanks Again

Monica

Super User

Re: Countx with some conditions

@MonicaElizondo Please try below using "New Column"

`ActualCount = CALCULATE(COUNTROWS(MatGroups),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))`
`ExpectCount = CALCULATE(DISTINCTCOUNT(MatGroups[MatGroup]),ALLEXCEPT(MatGroups,MatGroups[Country],MatGroups[Supplier]))`

Note - You can solve the same in "Power Query" as well using "GROUP BY" option..

Hope this helps !!

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Regular Visitor

Re: Countx with some conditions

@PattemManohar thanks a lot for your help.

It works perfect for me.

Thanks again

Monica

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,612)