Forum Discussion
ALL with multiple criterias from the same column
Dear PBI Community,
I want to calcculate Constant (number which should not change based on slicer selection, therefore I have createed athe following measure for this, which works fine
Constant_Picked_Lines_Per_day_2024 =
VAR selectedm = SELECTEDVALUE('ReportingData V_ATLAS_OL'[CalDate M])
VAR selectedy = "2024"
RETURN
CALCULATE(
[Total Picked Lines Per Weighted Working day],
ALL('ReportingData V_ATLAS_OL'),
'ReportingData V_ATLAS_OL'[Currency] = "LC",
'ReportingData V_ATLAS_OL'[Interval] = "MTD",
'ReportingData V_ATLAS_OL'[Version] = "AC",
'ReportingData V_ATLAS_OL'[CalDate M] = (selectedm),
'ReportingData V_ATLAS_OL'[CalDate Y] = "2024",
'ReportingData V_ATLAS_OL'[Country_ID]= "BAL",
)
But when I include other condition ( country also equal to AT ) for country ID column example as below, it gives me a blank value,
Constant_Picked_Lines_Per_day_2024 =
VAR selectedm = SELECTEDVALUE('ReportingData V_ATLAS_OL'[CalDate M])
VAR selectedy = "2024"
RETURN
CALCULATE(
[Total Picked Lines Per Weighted Working day],
ALL('ReportingData V_ATLAS_OL'),
'ReportingData V_ATLAS_OL'[Currency] = "LC",
'ReportingData V_ATLAS_OL'[Interval] = "MTD",
'ReportingData V_ATLAS_OL'[Version] = "AC",
'ReportingData V_ATLAS_OL'[CalDate M] = (selectedm),
'ReportingData V_ATLAS_OL'[CalDate Y] = "2024",
'ReportingData V_ATLAS_OL'[Country_ID]= "BAL",
'ReportingData V_ATLAS_OL'[Country_ID]= "AT"
)
Question :
How can I calculate the value of this constnat for countries AT, BAL, CZ, NL , HR i.e constant value having all these 5 countries included ?
is there any alternate function which can solve this?
Thank you very much in advance!!
This part of your code:
'ReportingData V_ATLAS_OL'[Country_ID]= "BAL",'ReportingData V_ATLAS_OL'[Country_ID]= "AT"is saying that Country ID must be both BAL and AT. This will never be true, because for each row in your table, the field Country_ID cannot simultaneously have both values. Filter conditions stack on top of one another. In other words, you're saying "give me records where: FilterA AND filterB AND filterC AND filterD" etc.... and that is why you're getting a blank result.What you want is this:'ReportingData V_ATLAS_OL'[Country_ID] IN { "BAL", "AT", "CZ", "NL" , "HR" }which is equivalent to saying "and Country_ID is BAL or AT or CZ or NL or HR"
///Mediocre Power BI Advice, but it's free///
1 Reply
kpostSolution Sage
This part of your code:
'ReportingData V_ATLAS_OL'[Country_ID]= "BAL",'ReportingData V_ATLAS_OL'[Country_ID]= "AT"is saying that Country ID must be both BAL and AT. This will never be true, because for each row in your table, the field Country_ID cannot simultaneously have both values. Filter conditions stack on top of one another. In other words, you're saying "give me records where: FilterA AND filterB AND filterC AND filterD" etc.... and that is why you're getting a blank result.What you want is this:'ReportingData V_ATLAS_OL'[Country_ID] IN { "BAL", "AT", "CZ", "NL" , "HR" }which is equivalent to saying "and Country_ID is BAL or AT or CZ or NL or HR"
///Mediocre Power BI Advice, but it's free///