Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team
I have two measures with V1 and V2 and two slicers as shown below.
I need to do some calculations when i select something from Slicer 1. Example as shown below with DAX formula.
1.Default = SUM(V1)
2.Ratio= SUM(V1)/SUM(V2)
Similarly when i select the Slicer 2 along with anything selected from Slicer 1. Example as shown below
1.Avg = (FormulaValue)/DISTINCTCOUNT(Name)
2.Sum = (FormulaValue).
My DAX formula will work only when i use slicer 1 and when i use both 1 and 2 slicer together , i am getting results only for some combinations. i am not getting results when something is selected in slicer 1 and "Sum" is selected in slicer 2 for example.
Please help me in this issue.
Regards.
Solved! Go to Solution.
If I understand correctly you want to change measures according to slicer?
You have to create a measure dimension looking something like this:
You need to create the following measures seperatly:
Ratio := CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2])) Default := CALCULATE(SUM(UnitValue[V1])
AVGR :=[Ratio]/DISTINCTCOUNT(Name)
AVGD := [Default]/DISTINCTCOUNT(Name)
Then you need a measure dimension to switch to the correct measure. The dimension would look something like this:
slicer1 slicer2
Ratio | AVG |
Ratio | SUM |
Default | AVG |
Default | SUM |
Measure Selection = SWITCH( TRUE(), VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGR], VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Ratio], VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGD],
VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Default] BLANK())
I think that this works properly.
Measure=
VAR Opt1 = SELECTEDVALUE([Slicer1])
VAR Opt2 = SELECTEDVALUE([Slicer2])
VAR FormulaValue=IF (Opt1="Ratio";
CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2]));
CALCULATE(SUM(UnitValue[V1])))
RETURN
IF (Opt2="Avg";(FormulaValue)/DISTINCTCOUNT(Name);
IF (Opt2="Sum";FormulaValue))
Hope this will work for you.
hI @Anonymous,
I am not getting values for any selection from Slicer 1 and 2 . I am getting just blank value.
Below is my DAX
If I understand correctly you want to change measures according to slicer?
You have to create a measure dimension looking something like this:
You need to create the following measures seperatly:
Ratio := CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2])) Default := CALCULATE(SUM(UnitValue[V1])
AVGR :=[Ratio]/DISTINCTCOUNT(Name)
AVGD := [Default]/DISTINCTCOUNT(Name)
Then you need a measure dimension to switch to the correct measure. The dimension would look something like this:
slicer1 slicer2
Ratio | AVG |
Ratio | SUM |
Default | AVG |
Default | SUM |
Measure Selection = SWITCH( TRUE(), VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGR], VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Ratio], VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGD],
VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Default] BLANK())
I think that this works properly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |