Filter Table By Summarised Set

[m1nkeh]

I have a table in my model where the date column is quite unreliable, and i need to find the sum total of 'measure' for when items in this table were first seen...

illustrated with this SQL, here we have two products but i only want to report a measure for when they are in their very first state

with cte as (    select 1 Id, 'Apple' Product, 'Quote' State, 100 Measure union
    select 2 Id, 'Apple' Product, 'Checking' State, 100 Measure union
    select 3 Id, 'Apple' Product, 'Quote' State, 50 Measure union
    select 4 Id, 'Apple' Product, 'Offered' State, 50 Measure union
    select 5 Id, 'Pear' Product, 'Quote' State, 80 Measure union
    select 6 Id, 'Pear' Product, 'Offered' State, 80 Measure
)


select
    cte.*
from cte
join (
    select
        Product
        ,min(id) FirstId
    from cte
    group by Product
) x
on
    cte.Product = x.Product
where
    cte.Id = x.FirstId;

But how can i achieve the same in dax??

I've been messing about for ages with summarize / addcolumns... and i can only get so far as:

[COLOR=#008080]evaluate[/COLOR]
[COLOR=#0000ff]addcolumns[/COLOR](
    [COLOR=#0000ff]summarize[/COLOR](
        [COLOR=#000000]'FactData'[/COLOR]
        ,[COLOR=#000000]'FactData'[Product][/COLOR]
    )
    ,[COLOR=#ff0000]"MinId"[/COLOR]
    ,[COLOR=#0000ff]calculate[/COLOR](
        [COLOR=#0000ff]min[/COLOR]([COLOR=#000000]'FactData'[Id][/COLOR])
    )
)

so, that quite plainly is the sub query in my T-SQL, but i can't for the life of my figure out how to use it to filter my main table, to only show items in the first state...

i've been trying to use the crossjoin() or generate() functions in order to replicate a cartesian join,

[COLOR=#008080]evaluate[/COLOR]
[COLOR=#0000ff]crossjoin[/COLOR](
    [COLOR=#000000]'FactData'[/COLOR]
    ,[COLOR=#0000ff]addcolumns[/COLOR](
        [COLOR=#0000ff]summarize[/COLOR](
            [COLOR=#000000]'FactData'[/COLOR]
            ,[COLOR=#000000]'FactData'[Product][/COLOR]
        )
        ,[COLOR=#ff0000]"MinId"[/COLOR]
        ,[COLOR=#0000ff]calculate[/COLOR](
            [COLOR=#0000ff]min[/COLOR]([COLOR=#000000]'FactData'[Id][/COLOR])
        )
    )
)

but just get the error:

Function CROSSJOIN does not allow two columns with the same name 'FactData'[Product].

I'm probably going about this the wrong way, but any help would be much appreciated - can upload a workbook if required (but i'll have to make it first)

 

[scottsen]

The answer may depend a bit on the size of your data (cuz we may care more if you have a ton), but ignoring (probably) the best answer (just filter your data in SQL or add another column from SQL with useful stuff), I would probably do something like...

A calc column:
IsMin = FactData[Id] = CALCULATE(MIN(FactData[Id]), ALL(FactData), FactData[Product] = EARLIER(FactData[Product))

It would be TRUE, for any row where it's the smallest ID within that Product. Nice to use a Boolean cuz they are small n such

Then, you can easily filter your measures with that...
TotalMeasure := CALCULATE(SUM(Measure), FactData[IsMin] = TRUE)

 

[m1nkeh]

sounds good, I'll give it a go.

I was driving home last night and thought about doing it in T-SQL, as there is quite a lot of data and that is going to be very fast.

However, I thought I'd try and expand my brain a bit by doing in DAX too... looks like i massively had the wrong end of the stick!!

I still don't 'get it' 100% but am getting there, cheers!!