Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Measure to Count by Last Stage

I am trying to write a pair of measures but I'm stuck. I'm working with a table that gives the history of clients through a series of processing stages. Each row represents a stage movement event, gi...
Need Help
greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

None of this needs to be in measure logic based on the requirements as described.

 

Two calculated columns:

 

// DAX calculated columns

LastStageFlag = 
FactStageTransition[Date]
	= CALCULATE(
		MAX( FactStageTransition[Date] )
		,ALLEXCEPT( FactStageTransition, FactStageTransition[ClientKey] )
	)


MaxStageFlag = 
FactStageTransition[Stage]
	= CALCULATE(
		MAX( FactStageTransition[Stage] )
		,ALLEXCEPT( FactStageTransition, FactStageTransition[ClientKey] )
	)

These indicate whether the row in the fact table represents the most recent stage for a given client, or the greatest stage for a given client.

 

Three measures:

// DAX measures
Clients = 
DISTINCTCOUNT( FactStageTransition[ClientKey] )

LastStageCount = 
CALCULATE(
	[Clients]
	,FactStageTransition[LastStageFlag]
)


MaxStageCount = 
CALCULATE(
	[Clients]
	,FactStageTransition[MaxStageFlag]
)

These use the flags we defined above. Use CALCULATE() to filter on those flags, and count the clients for that subset of the table.

 

Easy peasy.

You could also calculate those flags in PQ, but this is an area where DAX shines over M (or I just don't know the right M yet to make that as simple and performant).

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Heh, I always feel like I'm cheating when I do things in columns instead of measures. Yep, that method works. Thank you.

  • greggyb's avatar
    greggyb
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    Columns: Anything that user filters / interaction with reports cannot change.

     

    Measures: Anything that user interaction can change.

     

     

    More specifically, my usual argument is that if it's known at refresh-time and unchanging in the face of filters / interaction, it should be a part of ETL, not done in the model. This sort of column logic is actually much easier to do in DAX than in Power Query, and I don't know what your ultimate source is. I'd always recommend pushing changes / transformations / new columns as far up the source chain as possible, but if this has to be done in DAX, it's not so bad.