Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am a newbei to DAX.I have two tables as follows:
customerID | customername |
1 | aaa |
2 | bbb |
3 | ccc |
ProductID | SalesCustomerID | Range | Quantity |
1000 | 3 | H | 10 |
1004 | 3 | C | 15 |
1009 | 3 | ||
2000 | 1 | K | 20 |
1007 | 1 | C | 25 |
1002 | 1 | ||
1005 | 2 | A | 16 |
1003 | 2 | K | 18 |
I want to populate the Range per customer where the Quantity is the highest.Th expected output is :
customerID | customername | Highestrange |
1 | aaa | C |
2 | bbb | K |
3 | ccc | C |
Thank you in Advance!!!
Solved! Go to Solution.
Hi @Anonymous
You can create a Measure as the following.
HighestRange =
VAR max__ =
MAXX (
FILTER (
Quantity,
Quantity[SalesCustomerID] = MAX ( Quantity[SalesCustomerID] )
),
Quantity[Quantity]
)
RETURN
CALCULATE ( MAX ( Quantity[Range] ), Quantity[Quantity] = max__ )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @Anonymous
You can create a Measure as the following.
HighestRange =
VAR max__ =
MAXX (
FILTER (
Quantity,
Quantity[SalesCustomerID] = MAX ( Quantity[SalesCustomerID] )
),
Quantity[Quantity]
)
RETURN
CALCULATE ( MAX ( Quantity[Range] ), Quantity[Quantity] = max__ )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
@Anonymous , Create a new measure like this and use that in visual
Highestrange =
VAR __id = MAX ('Table'[SalesCustomerID] )
VAR __date = CALCULATE ( MAX('Table'[Quantity] ), ALLSELECTED ('Table' ), 'Table'[SalesCustomerID] = __id )
CALCULATE ( MAX ('Table'[Range] ), VALUES ('Table'[SalesCustomerID] ),'Table'[SalesCustomerID] = __id,'Table'[Quantity] = __date )
Thank you for your response!!! But I am getting the output for customer bbb highestrange = C, where as am expecting K
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |