COUNT with specific conditions - DAX formula help

[Anonymous]

Hello @all,

i could need some help on this given example:

What i'm looking for is the following:

Number (Count) of IDs for which type 1 exists and the requirement is fulfilled, but no type 2 exists yet.

Thanks in advance!

[AnthonyTilley · Accepted solution]

i woudl create a calclated colunm that tells you which rows meet the criteria then use a measure to count.

calculated colunm 

CountSpec =

-- first get the id for thew row

var uid = Table1[id]

--then filter the table to just those with a matching id and get the max type

var ty = CALCULATE(MAX(Table1[type]),filter(table1,Table1[id]=uid))

-- then get the value for rquirment

var req = Table1[requirement check]

--nexct check to see if each of the rows meet the criteria

var reqcheck = IF(req = "Y", true(),false())

var tycheck = if(ty = 1, TRUE(),FALSE())

-- finally compare the two checks and return 1 if both are true

var ret = if(and(reqcheck,tycheck),1,0)

Return ret

measure

Measure = sum(Table1[CountSpec])

[Anonymous]

it does exactly what i need it to, thank you!