locked
COUNTIF to DAX Expression RRS feed

  • Question

  • Hello Guys,

    I have several COUNTIF formulas, but I don't know how to be able to change it to DAX expression. See below formula for example:

    TOTAL COUNT of TODAY: =COUNTIFS(Sheet1!$D$9:$D$1004,'Sheet2!$A22,Sheet1!$E$9:$E$1004,"NCR",Sheet1!$H$9:$H$1004,"<="&TODAY,Sheet1!$N$9:$N$1004,">"&TODAY)+COUNTIFS(Sheet1!$D$9:$D$1004,'Sheet2'!$A22,Sheet1!$E$9:$E$1004,"NCR",Sheet1!$H$9:$H$1004,"<="&TODAY,Sheet!$N$9:$N$1004,"="&"")

    TOTAL COUNT of 1 WEEK AGO to PRESENT:

    =COUNTIFS(Sheet!$D$9:$D$1004,'Sheet2'!$A22,Sheet1!$E$9:$E$1004,"NCR",Sheet1!$H$9:$H$1004,"<="&WEEKAGO,Sheet1!$N$9:$N$1004,">"&WEEKAGO)+COUNTIFS(Sheet1!$D$9:$D$1004,'Sheet2'!$A22,Sheet1!$E$9:$E$1004,"NCR",Sheet1!$H$9:$H$1004,"<="&WEEKAGO,Sheet1!$N$9:$N$1004,"="&"")

    Calendar Table: Today: =Today(), Week Ago: =Today()-7, Two Weeks Ago: =Today()-14, Month Ago: =Today()-30, Two Months Ago: =Today()-60, Six Months Ago: =Today()-180, Year Ago: =Today()-365

    I'm very new to PowerPivot and I have little idea about making DAX expression I will be able to play around with the DAX expression if I will be given at least the sequence or pattern of DAX expression that will provide the same result with above excel COUNTIF formulas.

    I already have PowerPivot table (Table1) were Sheet1 Column D is "Recipient Dept", Sheet2 Column A lookup value to "Recipient Dept", Sheet1 Column E "Document Type", Sheet1 Column N "Date Issued" and Sheet1 Column N "Date Verification".

    Also, I have a calendar table in PowerPivot namely as follows; Date Issed:"Calendar_Issued", Date Verification: "Calendar_Ver" with relationship to Table1.

    Any update will be very much appreciated.

    Thanks!

    Monday, May 23, 2016 5:35 AM

Answers

  • Hi Mielkew,

    Welcome to Power Pivot ;-)

    One would implement the 'IF' part of your Excel formulas through a DAX filter. I normally start with an unfiltered calculation like

    TotalCount:=COUNT(<whatever column you want to count>)

    then, apply any filter you want through CALCULATE, e.g.

    TotalCountofCategoryA:=CALCULATE([TotalCount],CategoryTable[Category]="A")

    When you have more complex filters to apply than the simple one above, use FILTER to iterate over a table and check a filter expression for every row in the table, e.g.

    TotalCountofToday:=CALCULATE([TotalCount],FILTER(Calendar,Calendar[Date]=TODAY()))

    Your calendar table should contain a row for each day, but you don't need to have columns for "Week Ago" etc. These can be calculated easily with time intelligence functions in DAX.

    There's much more to DAX filters than can be explained in a post, but these are the very basics.

    • Marked as answer by Mielkew Monday, May 23, 2016 7:43 AM
    Monday, May 23, 2016 6:31 AM
    Answerer

All replies

  • Hi Mielkew,

    Welcome to Power Pivot ;-)

    One would implement the 'IF' part of your Excel formulas through a DAX filter. I normally start with an unfiltered calculation like

    TotalCount:=COUNT(<whatever column you want to count>)

    then, apply any filter you want through CALCULATE, e.g.

    TotalCountofCategoryA:=CALCULATE([TotalCount],CategoryTable[Category]="A")

    When you have more complex filters to apply than the simple one above, use FILTER to iterate over a table and check a filter expression for every row in the table, e.g.

    TotalCountofToday:=CALCULATE([TotalCount],FILTER(Calendar,Calendar[Date]=TODAY()))

    Your calendar table should contain a row for each day, but you don't need to have columns for "Week Ago" etc. These can be calculated easily with time intelligence functions in DAX.

    There's much more to DAX filters than can be explained in a post, but these are the very basics.

    • Marked as answer by Mielkew Monday, May 23, 2016 7:43 AM
    Monday, May 23, 2016 6:31 AM
    Answerer
  • Hi Mielkew,

    Welcome to Power Pivot ;-)

    One would implement the 'IF' part of your Excel formulas through a DAX filter. I normally start with an unfiltered calculation like

    TotalCount:=COUNT(<whatever column you want to count>)

    then, apply any filter you want through CALCULATE, e.g.

    TotalCountofCategoryA:=CALCULATE([TotalCount],CategoryTable[Category]="A")

    When you have more complex filters to apply than the simple one above, use FILTER to iterate over a table and check a filter expression for every row in the table, e.g.

    TotalCountofToday:=CALCULATE([TotalCount],FILTER(Calendar,Calendar[Date]=TODAY()))

    Your calendar table should contain a row for each day, but you don't need to have columns for "Week Ago" etc. These can be calculated easily with time intelligence functions in DAX.

    There's much more to DAX filters than can be explained in a post, but these are the very basics.

    Thanks for the tip!

    I will try to work something out, I well get back here if I can't make the result that I want!

    Monday, May 23, 2016 7:45 AM