If statement won't work

[w21450]

Hi everyone

I have a problem with a simple if statement that won't work and I just can't seem to figure out what is wrong. I'm saving the calculation to a Master Dimension

My formula is the following: 

If(count({$<[Opgavestatus (Opgave)]={"LUKKET", "INVALID"},Dato = {'($(vDynMaxDate))'},Datotype = {'OpgaveStatus'}>} Distinct [Opgave_id (Opgave)])/count(distinct([Opgave_id (Opgave)])) > 0,'G1', 'Nej')

So basically what's going on, is that i'm doing a ratio calculation, and then i'm checking whether the result is greater than 0 or not. If the result is greater that 0 it should return the string 'G1' else the string 'Nej'.

I then save this to a Master Dimension I want to use in my app. But when I try to use it in a table for example, it just says 'invalid dimension'.

Can anyone helt me understand what I am doing wrong?

By the way, the actual formula i'm using is a lot longer and has many more nested if statements. I just shortened it a bit to make it easier to explain the problem. The issue is the same whether i used the simple formula above or the long one.

Best,

Peter

[Kushal_Chawda · Accepted solution]

Well, as you are using this in dimension you may need to use aggr function to work it correctly

If(aggr(count({$<[Opgavestatus (Opgave)]={"LUKKET", "INVALID"},Dato = {'($(vDynMaxDate))'},Datotype = {'OpgaveStatus'}>} Distinct [Opgave_id (Opgave)])/count(distinct([Opgave_id (Opgave)])) ,YourDimension)> 0,'G1', 'Nej')

else use this expression in measure instead

[NitinK7]

Hi,

Can you attach here some data ?

[w21450]

Thanks a lot. The aggr worked, in order to make the Dimension show the correct groups. I didn't know you needed to use the aggr to do that. Thanks!