Count If with 2 criteria in DAX

[thomsog1]

Hi All,

I'm trying to do a count if based on two columns. The logic is that I want to find out if an ID Tag already existed in a previous month, or if its new this month.

In excel it would look like as follows...

In powerquery I managed to get it working (albeit to avoid circular references I needed to create it as a new table), but I'm curious how I would go about acheiving this in DAX? I tried both COUNTROWS() and LOOKUPVALUE() based on other forum questions and neither worked.

Thanks

[thomsog1]

Ah brilliant thanks, I'll test this out.

[lbendlin · Accepted solution]

Power BI has no concept of "Before" and "After" .  You need to provide an index column, or indicate which combination of columns can be used for that purpose

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC7DYBADATRXhyfdIztbtAFfPqvASQCgkk3eNLsvgdHjCAnM7ck1nin09Pl6daUttJW2kpbZatsla2y1bbaVttqW99f+C+cjbNxNs7G2TgbB+EgHPRP6wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Tag" = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Tag", type text}, {"Period", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if 
Table.RowCount(Table.SelectRows(#"Changed Type",(k)=> k[ID Tag]=[ID Tag] and k[Period]<[Period]))>0
 then "Existing" else "New")
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Or in DAX :

Status DAX = 
var p=[Period]
return if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[ID Tag]),'Table'[Period]<p)>0,"Existing","New")