Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Community Champion

@Anonymous 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 PBI Community Champion

6 REPLIES 6
Solution Sage

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:

Anonymous
Not applicable

@MarkLaf thanks for your reply;

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

Community Champion

@Anonymous 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 PBI Community Champion

Anonymous
Not applicable

@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

Community Champion

@Anonymous 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 PBI Community Champion

Anonymous
Not applicable

@PattemManohar thanks a lot for your help.

It works perfect for me.

Thanks again

Monica

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors