topic Re: Unique Values based on Different Filters in DAX Commands and Tips

Unique Values based on Different Filters

Anonymous — Fri, 01 May 2020 05:49:06 GMT

Hi All,

I am struggling to find the out the unique values based on the different filters. Here below is my sample data table and I want to find out the Associate name based on the highest spend by Division By Subcategory. For below table the expected one answer would be like below.

I want the same figures for from the data

Expected Result: I want all details

Associate Name	Sub Category	Division
C1	Group	Road
A1	Labour	Utilities
A1	Waste	Construction

Data Table

Sub Category	Division	Associate Name	Total Spend
Labour	Utilities	A1	8
Labour	Utilities	B1	15
Labour	Utilities	C1	12
Labour	Utilities	D2	9
Labour	Utilities	A1	45
Hardware	Engineering	C1	31
Hardware	Engineering	D2	39
Hardware	Engineering	A1	42
Waste	Engineering	C1	2
Waste	Construction	D2	13
Waste	Construction	A1	28
Waste	Construction	A4	15
Work	Construction	A5	43
Work	Infrastructure	A1	8
Work	Infrastructure	C1	48
Work	Infrastructure	D2	21
Group	Infrastructure	A1	37
Group	Road	A1	19
Group	Road	C1	45
Group	Road	D2	19

Note: I am using direct query as data from source system.

Please help me.

Re: Unique Values based on Different Filters

Anonymous — Fri, 01 May 2020 21:00:29 GMT

File attached.

Best

Re: Unique Values based on Different Filters

Anonymous — Sat, 02 May 2020 15:27:42 GMT

Hi,

I am not getting the correct or unique value when I am adding all those columns in Table visualisation. Please find the attached spreadsheet. When I am adding sub-categories it is not showing the higest spend division, instead of it is showing Division and several subcategories. But answer should be the assoicate name have the highest spend in which subcategory and its respective division name i.e. Check given below example

Associate Name	Division	SubCategory	Spend
A1	Asset	labour	1.4m

Please find the results I got after applying DAX function you shared.

Associate Name	Division	SubCategory	Total spend
P1	Group	HIR	871250
P1	Services	HIR	816872
P1	Utilities	HIR	507428
J1	Asset	General	368213
J1	Utilities	General	180877
R1	Infrastructure	SAFETY	150041
JJ	Mining	REMOVAL	112983
TO	Asset	NIFORMS	98109
RENTAL	E & C	General	89769
DI	Services	WHOL	79230
R1	Services	SAFETY	66901
JJ	Utilities	REMOVAL	61828
DI	Mining	WHOL	50426
MA	Utilities	SAFETY	36554
TO	E & C	NIFORMS	34771
PORTER	Infrastructure	HIR	14487
JJ	Infrastructure	REMOVAL	9276
JJ	Services	REMOVAL	8675
RS	Infrastructure	ELECTRICAL	7104
RS	Rolling	ELECTRICAL	6408
TO	Infrastructure	NIFORMS	4622
MA	E & C	SAFETY	3358
DI	Group	WHOL	2682
A1	Asset	LABOUR	1045049
STAFF	Mining	LABOUR	714210
GTE	Infrastructure	LABOUR	609906
POWER	Services	LABOUR	518041
KI	Asset	HIR	393785
EFFECTIVE	Group	LABOUR	355823
RPM HIRE	Services	General	346183
RO	Asset	ELECTRICAL	287974
PETER	Utilities	LABOUR	240067
KEN	Infrastructure	General	232636
PILBARA	E & C	HIR	177883
MID	Utilities	ELECTRICAL	176948
MYELEC	E & C	ELECTRICAL	169325
B1	Services	ELECTRICAL	133544
JR	Asset	REMOVAL	115107
E A	Mining	ELECTRICAL	66420
B2	Utilities	WHOL	61633
FLINDANE	Group	General	35727
RWW	Services	NIFORMS	30547
BU	Asset	SAFETY	30315
THE	Mining	NIFORMS	28214
B4	Asset	WHOL	25808
C1	Rolling	SAFETY	25259
SYSTEMS	Mining	SAFETY	25020
MULTI	Mining	General	20736
BUSB	Mining	HIR	18786
HIP POCKET	Utilities	NIFORMS	6425
DRS	Group	NIFORMS	6179
MITRE 10	Infrastructure	WHOL	3600
ELECTRIC	Group	ELECTRICAL	3547
RECYCLING	E & C	REMOVAL	3160
AU	Rolling	NIFORMS	2964
TMH	Group	SAFETY	1681

Thanks

Uphar