CODEWITHSUNDEEP
daxpowerpivot
Hazel
Hazel

Reputation: 43

Countif in DAX PowerPivot

I need to calculate the average weekly quantity of a month in Power Pivot, so I need to count how many weeks in the column of "Wkly #" on the 1st table "Fcst Volume" from the 2nd table "Period". For example, I need it to be 5 for the 1st "Wkly #" as there are 5 "P03" on the 2nd table and I need it to be 4 for the 2nd "Wkly #" as there are 4 "P04" on the 2nd table. Can someone please tell me how to do Countif in DAX? Many thanks

Fcst Volume

Period

Upvotes: 0

Views: 3459

Answers (2)

smpa01
smpa01

Reputation: 4282

When you do countif/sumif, it is an aggregation by partition. The direct dax equivalent is ALLEXCEPT

If you have table like following Solution

You can write the calculated column like following

=CALCULATE(COUNT(Table1[Column1]),ALLEXCEPT(Table1,Table1[Column1]))

Upvotes: 0

Jos Woolley
Jos Woolley

Reputation: 9052

Create a new column in Table1:

=CALCULATE(COUNTROWS('Table2'),FILTER(Table2,'Table2'[Period]='Table1'[Period]))

Upvotes: 0

Related Questions