How to create a calculated dimension that excludes certain values, showing the rest

[educastri83]

Hi! 

I have 2 expressions that create 2 calculated dimensions:

1) First dimension - Show all class types when KPI is equal to invoice 

=Aggr(if(Count(DISTINCT {<[Class] = {"=(Count({< [CURRENCY]= {'EUR'}, KPI={'INVOICE'} >} DISTINCT [Sub-Apertura])> 0

) "}>}[Class] ), 'All Class',

),[Class])

2) Second dimension: it only shows the values ​​of the country Ecuador, for the monthYear Jan.-2024 and class is fictitious

=Aggr(
if(
count(distinct {<KPI={'INVOICE'},
[Class]={'Ficticious'},
[CURRENCY]={'EUR'},
Country ={'Ecuador'},
MonthYear={'jan.-2024'}>} Country) > 0,
'Check ECU'
), Country, MonthYear, [Class]
)

I am needing is a new calculated dimension that shows me all the values ​​of expression 1 but without showing those that expression 2 would return.

I have tried different ways but I can't get it to show me what I need. Thanks for the help

[marksouzacosta · Accepted solution]

Hi @educastri83 ,

I would definitely try to move this logic to the Load Script and create all the needed dimensions there.
It is hard for me to suggest you an approach to do that since I don't know your Data Model and your Load Script.

Regards,

Mark Costa

[educastri83]

You're right Mark.

Simply form a key that will concatenate those fields and wow, it worked.

Calculated dimension: 

=Aggr(if(Count(DISTINCT {<Key = {"=(Count({<KPI={'INVOICE'}, [CURRENCY]={'EUR'}, Key -={'jan.- 2024 - INVOICE - Ficticious - Ecuador'}>} DISTINCT Key)> 0

) "}>} KEY ) 

, 'Without Outlier ECU'

), KEY )

In load script "KEY" field its MonthYear & " - " & KPI & " - " & Class & " - "  Country as KEY