Return MAX of a Distinct Count per Category

[chudson]

Hi,

See the below visual table I have.  I'm trying to created a measure where I can remove the "SubSet" column from my visualization and return the maxium of the count column for each Set Category.  See example for current and desired.

Current

Set	SubSet	Count
A	A.1	1
A	A.2	3
A	A.3	2
A	A.4	1
B	B.1	1
B	B.2	1
B	B.3	4
C	C.1	3
C	C.2	1

Desired (remove Subset and have return a value of the max count for reach Set category)

Set	Max of Subset Counts
A	3
B	4
C	3

Thanks,

[Hydramh2]

Hi , can I ask how you wrote the Desire column?

Which measure did you use?

I want to replicate a similar column in my report but I'm not able to do that. Thanks

[Ashish_Mathur]

Hi,

It is not a measure - it is a calculated column formula.

[chudson]

Hi @Hydramh2

Below are 2 versions of calculated columns I used.  You would obviously have to swap in the right fields and names for yours but you can see the DAX i used.

CALCULATE(MAX('SET CONTROL HISTORY'[SetTypeCount]),FILTER('SET CONTROL HISTORY','SET CONTROL HISTORY'[customername]=EARLIER('SET CONTROL HISTORY'[customername])&&'SET CONTROL HISTORY'[SETFAMILY]=EARLIER('SET CONTROL HISTORY'[SETFAMILY])))

SUMX(FILTER('SET CONTROL HISTORY','SET CONTROL HISTORY'[customername]=EARLIER('SET CONTROL HISTORY'[customername])&&'SET CONTROL HISTORY'[SetTypeDesc]=EARLIER('SET CONTROL HISTORY'[SetTypeDesc])),'SET CONTROL HISTORY'[Count1])

[edhans]

In the visual for a Table, add the Set field to values. Create this measure:

Max Set Count = MAX(TableData[Count])

Add that measure to the values.

Change the table name "tableData" to whatever your table name is.

[chudson]

Hi @edhans,

The Count column is a count of subset in a table visualization, both those tables are already in visulizations but I need a measure to get the max of each.

Thanks,

[edhans]

Then use MAX(YourCountMeasure)

IF that isn't what you need, then please post sample data or CLEARLY explain what everything is. 

[chudson]

I tried to attach a dummy data set but apparently I cannot since I'm only a regular forum user.  Below is a pivot from my dataset replicating a table visulization (dummied down since my dataset is really thousands of lines).  I basically want to create either a calculated measure or column that will give me the max of the "Total" column by SetFamily per customer from looking at the setdescription. 

I want the largest of the total # from the set descriptions under each set family by customer.

I just don't know the perfect DAX to get to this.  Once I have that calculation I want to remove the setdescription from my visual so it only shows the Customer, SetFamily & Max Count column I just have those in there so you can understand my desired result based on the data.

Let me know if this makes sense.

customername	SetFamily	SetDescription	Total	Desire Max result for setfamily by setdescription
Customer1	IC1	IC1 SET 6	3	3
Customer1	IC1	IC1 SET1	2	3
Customer1	IC1	IC1 SET2	2	3
Customer1	IC1	IC1 SET3	2	3
Customer1	IC1	IC1 SET4	3	3
Customer2	AVA	AVA SET	3	3
Customer2	BAT3	BAT SET5	3	3
Customer2	IC1	IC1 SET 6	3	6
Customer2	IC1	IC1 SET1	3	6
Customer2	IC1	IC1 SET2	2	6
Customer2	IC1	IC1 SET3	3	6
Customer2	IC1	IC1 SET4	6	6
Customer2	NCP	NCP SET 1	3	3
Customer2	NCP	NCP SET 2	1	3
Customer2	NCP	NCP SET 3	1	3
Customer2	NSD	NSD SET 1	3	3
Customer2	NSD	NSD SET 2	1	3
Customer2	TLX	TLX SET 1	3	3

Thanks,

[Hydramh2]

Hi @chudson , can I ask you how you wrote the Desire column?

Which measure did you use?

I want to replicate a similar column in my report but I'm not able to do that. Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=CALCULATE(MAX(Data[Total]),FILTER(Data,Data[customername]=EARLIER(Data[customername])&&Data[SetFamily]=EARLIER(Data[SetFamily])))

Hope this helps.

[chudson]

Hi @Ashish_Mathur,

Yeah this worked as a calculated column.  Is that the only way or is there a measure that can be done to get this result?

[Ashish_Mathur]

Hi,

Try these measures

Measure=SUM(Data[Total])

Max=MAXX(ALL(Data[SetDescription]),[Measure])

Hope this helps.

[edhans]

If your visual has Customer and Set in it, then just =MAX(tablename[Total]) will work as Customer and Set will provide the filter context for it.

If not then you'd need to do something along the lines of this (typing free hand here, so check the syntax)

=
CALCULATE(
    MAX(tablename[Total],
    ALLEXCEPT(tablename,[Customer],[Set])
)

No matter what you put in the table visual now, it will get the MAX value of TOTAL taking customer and set into account, but ignore anything else.