Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys,
I have a datatabel like this
Custmer count = the total number of customer with the assets on the right.
I have also created a field paramter
Parameter =
(("Mutual Fund" , NAMEOF("measuretable"[sum_of_mutual_fund],0),
("GIC" , NAMEOF("measuretable"[sum_of_gic],1),
("Cash" , NAMEOF("measuretable"[sum_of_cash],2))
Thus, now I have two chart:
Sum of Total customer = 25
By selecting the parameter measure, i can get Mutual fund = 20, GIC = 20, Cash = 20.
However, I am trying to connect the dots between them.
I want to create a measure lets say if i select Mutual fund on the paramenter, it will have Mutual fund = 20 and total customer with Mutual fund = 1+3+3+5 = 12.
May I know if anyone can help?
Thank you!
Solved! Go to Solution.
Hello,
Taking the data you have presented at face value, you can consider the following measure:
CustomerCount =
VAR _MF = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[Mutual Funds])))
VAR _GIC = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[GIC])))
VAR _SGD = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[CASH])))
RETURN
if(
HASONEVALUE(Parameter[Parameter Order]),
SWITCH(
VALUES(Parameter[Parameter Order]),
0, _MF,
1, _GIC,
2, _SGD
)
)
One note: in order to get the table to work and get the result as intended, I did have to create an additional index column so that each customer count can be attributed to a single "index". I had initial problems where it would combine similar customer counts as a single entity, if that makes sense?
My parameter looks like this:
Parameter = {
("Mutual Funds", NAMEOF('DataTable'[Mutual Funds]), 0),
("GIC", NAMEOF('DataTable'[GIC]), 1),
("CASH", NAMEOF('DataTable'[CASH]), 2)
}
Hello,
Taking the data you have presented at face value, you can consider the following measure:
CustomerCount =
VAR _MF = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[Mutual Funds])))
VAR _GIC = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[GIC])))
VAR _SGD = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[CASH])))
RETURN
if(
HASONEVALUE(Parameter[Parameter Order]),
SWITCH(
VALUES(Parameter[Parameter Order]),
0, _MF,
1, _GIC,
2, _SGD
)
)
One note: in order to get the table to work and get the result as intended, I did have to create an additional index column so that each customer count can be attributed to a single "index". I had initial problems where it would combine similar customer counts as a single entity, if that makes sense?
My parameter looks like this:
Parameter = {
("Mutual Funds", NAMEOF('DataTable'[Mutual Funds]), 0),
("GIC", NAMEOF('DataTable'[GIC]), 1),
("CASH", NAMEOF('DataTable'[CASH]), 2)
}
A follow up question here my friend,
This Measure works perfectly if I pick one Parameter.
However, May I know if you can show me a trick how does it work with two or even three parameters?
For example,
Mutual fund = 12 customer
GIC = 11 customer
Cash = 12 Customer.
Now I want to see how many customer I have with both GIC and Cash, so I multi-select both.
The total is 40 and customer count is 3+3 = 6 (because only these 2 groups of customer have GIC and Cash at the same time).
Please let me know if you have any idea.
Thank you!
Works like a charm! Much appreciated
User | Count |
---|---|
84 | |
71 | |
71 | |
68 | |
55 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |