Forum Discussion
missing values after measure
Hey! I do not know why but when I use the following formula and then use a slicer to select some values, it does not give me every value that is corresponding to the slicer, there is a lot missing. Thank You !
Indicator for AND Condition with Exclusion =
---------------------------------------
//get the relevant slicer values
VAR __Inclusionslicervalues =
VALUES ( 'Liste de mineure groupe'[Groupement (Mineure)] )
VAR __Exclusionlicervalues =
VALUES ( 'Disconnected liste mineure'[Groupement (Mineure)] )
--create a virtual table with the selected values from both the slicers
VAR __AllSelectedSlicerValues =
UNION ( __Inclusionslicervalues, __Exclusionlicervalues )
----------------------------------------
--get the slicer values as a string
VAR _AllslicerValue =
CONCATENATEX (
__AllSelectedSlicerValues,
'Liste de mineure groupe'[Groupement (Mineure)],
",",
'Liste de mineure groupe'[Groupement (Mineure)], ASC
)
VAR _InclusionSlicerValue =
CONCATENATEX (
__Inclusionslicervalues,
'Liste de mineure groupe'[Groupement (Mineure)],
",",
'Liste de mineure groupe'[Groupement (Mineure)], ASC
)
----------------------------------
--get the relevant IDs cond values which are filtered in the inclusion filter as string
VAR _CondValue =
CONCATENATEX (
'2021 CY Analyse colo par cat et',
'2021 CY Analyse colo par cat et'[Groupement (Mineure)],
",",
'2021 CY Analyse colo par cat et'[Groupement (Mineure)], ASC
)
--get the relevant IDs all the cond values remvoing the filter coming in from the inclusion filter
VAR _Allcondvalue =
CONCATENATEX (
CALCULATETABLE (
'2021 CY Analyse colo par cat et',
REMOVEFILTERS ( 'Liste de mineure groupe'[Groupement (Mineure)] ),
'Liste de mineure groupe'[Groupement (Mineure)] IN __AllSelectedSlicerValues
),
'2021 CY Analyse colo par cat et'[Groupement (Mineure)],
",",
'2021 CY Analyse colo par cat et'[Groupement (Mineure)], ASC
)
-----------------------------------
//Return Values based on Filter Condition
--================================================
--to use when both the ID slicers are filtered
--select records only with selection from all inclusion slicer values and from that set exclude any IDs which has values from the exclusion slicer
VAR _returnValue1 =
IF (
_CondValue = _InclusionSlicerValue
&& _Allcondvalue <> _AllslicerValue,
1,
BLANK ()
)
--================================================
--to use when both the filters are not selected
--in this case show all the IDs
VAR _returnValue2 = 1
--================================================
--to use when only inclusion selector is selected
--in this case only select the IDS which has all the values selected in the slicer
VAR _returnValue3 =
IF ( _CondValue = _InclusionSlicerValue, 1, BLANK () )
--================================================
--to use when only exclusion selector is selected
--in this case exclude all IDs which has excluded slicer values in the cond field
VAR _returnValue4 =
IF ( __Exclusionlicervalues IN VALUES ( '2021 CY Analyse colo par cat et'[Groupement (Mineure)] ), BLANK (), 1 ) --================================================
-----------------------------------
RETURN
//based on the slicer selections, return the correct value
SWITCH (
TRUE (),
ISFILTERED ( 'Liste de mineure groupe'[Groupement (Mineure)] ) && ISFILTERED ( 'Disconnected liste mineure'[Groupement (Mineure)] ), _returnValue1,
ISFILTERED ( 'Liste de mineure groupe'[Groupement (Mineure)] ) = FALSE ()
&& ISFILTERED ('Disconnected liste mineure'[Groupement (Mineure)] ) = FALSE (), _returnValue2,
ISFILTERED ( 'Liste de mineure groupe'[Groupement (Mineure)] ) = TRUE ()
&& ISFILTERED ( 'Disconnected liste mineure'[Groupement (Mineure)] ) = FALSE (), _returnValue3,
ISFILTERED ( 'Liste de mineure groupe'[Groupement (Mineure)] ) = FALSE ()
&& ISFILTERED ( 'Disconnected liste mineure'[Groupement (Mineure)] ), _returnValue4,
BLANK ()
)
No RepliesBe the first to reply