Count distinct based on multiple conditions

[ssze]

I have a table set (this is a summary selection for testing my DAX) - everything done in 2019

I want to do a count distinct of skids that has the latest current_status.

e.g. T5 went from under construction in Feb 19 to Built in Apr-19 then to Temp in Jun-19.

whereas Skid 33 went from under construction in Feb 19 to built in Apr-19.

(there will be cases where the latest status is only under construction as well).

I have been googling to check filter etc but am at a loss!

Just wonder if anyone has experience in this sort of double filtering (latest date and latest current status)?

Thank you in advance!

[ERD]

Hello @ssze ,

I'm not sure about a measure, but in case of a calculated table here is the code that will give the expected output:

EDITED (It appears that SUMMARIZE is not recommended to be used to add columns)

var latestStatusPerSkid  = 
SUMMARIZECOLUMNS (
SkidTable[Skid], 
	"CS",
	MAXX( FILTER(SkidTable, SkidTable[Month] = MAX(SkidTable[Month]))
	,SkidTable[Current_status])
)
RETURN
ADDCOLUMNS(
VALUES(SkidTable[Current_status]), 
"Skids", 
COUNTROWS(FILTER(latestStatusPerSkid, SkidTable[Current_status] = [CS]))
)

[ssze]

Thank you heaps @ERD ! it worked! 

One more question though - my DAX writing skills isn't quite there yet and i tried a few different ways, but my forest column in the table has multiple forests usually. How can i addcolumns with a forest too? 

i.e. with your help i have this, and would like an extra column there for forests (so Forest A, Built, Under Construction, Temp; Forest B, Built, Under Construction, Temp etcetc)

Thank you heaps!

[ERD · Accepted solution]

@ssze ,

As usual, there are multiple ways in DAX to achieve the result and it depends on the model and the requirements. You can try the next option (it might be not the best one, just try to play with it):

VAR mostRecentSkids =
    SUMMARIZECOLUMNS ( SkidTable3[Forest], SkidTable3[Skid],
        "mostRecentDate", CALCULATE ( MAX ( SkidTable3[Month] ) )
    )
VAR filteredSkidTable =
    CALCULATETABLE (
        SkidTable3,
        TREATAS ( MostRecentSkids, SkidTable3[Forest], SkidTable3[Skid], SkidTable3[Month] )
    )
RETURN
    SUMMARIZECOLUMNS ( SkidTable3[Forest], SkidTable3[Current_status],
        "Skids",
            VAR SkidsAmt = COUNTROWS (
                    FILTER (
                        filteredSkidTable,
                        [Forest] = SELECTEDVALUE ( SkidTable3[Forest] )
                            && [Current_status] = SELECTEDVALUE ( SkidTable3[Current_status] )
                    )
                )
            RETURN COALESCE ( SkidsAmt, 0 )
    )

By the way, I've also changed the previous one as I've got that it is not recommended to use summarize function to add columns.

[ssze]

Thank you heaps - it worked wonders 🙂

[ssze]

Thank you @amitchandak!

here is the input table (i think don't worry about OBJECTID because it's from a database)

OBJECT ID	Forest	Skid	Current_Status	Previous Status	Month
59	A	35	Built	Planned	Feb-19
51	A	37	Built	Planned	Mar-19
113	A	33	Built	Under Construction	Apr-19
114	A	34	Built	Under Construction	Apr-19
115	A	T5	Built	Under Construction	Apr-19
179	A	T7	Built	Planned	Aug-19
180	A	32A	Built	Planned	Aug-19
181	A	1	Built	Planned	Aug-19
149	A	T5	Temp	Built	Jun-19
60	A	T5	Under Construction	-	Feb-19
61	A	34	Under Construction	Planned	Feb-19
62	A	33	Under Construction	Planned	Feb-19

and the output i am looking for would be like this:

(counting the number of skids according to the latest status)

Current Status	Number of Skids	My Note
Built	7	Skid 35, 37,33 ,34, T7,32A,1 (because T5 latest status is Temp)
Temp	1	T5
Under Construction	0	Because all 3 are now built or is temp

I hope this makes sense 🙂 Thank you again for your help.

Cheers,Silvia

[amitchandak]

@ssze , the expected output is not very clear.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.