Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to change the way a measure is calculated based on the stage of a drill down. I am able to correctly visualize the data independently.
Level A dimension is defined as: Aggr(Count({<[Status]={'OK'}>}[Event#]),[Name])
and the corresponding measure is: Count(Distinct{<[Status]={'OK'}>}[Name])
When visualized as a bar chart, it displays x= number of events; and y=frequency that [Name] had the same number of events (eg. x=11 and y=5: Five Names have Eleven event occurrences).
Level B dimension is defined as: [Name]
and the corresponding measure is: Count({<[Status]={'OK'}>}[Event#])
When visualized as a bar chart, it displays x=name; and y=number of events.
My goal is that when the user selects 11, the drill down displays the 5 names and shows the number of events associated with each name.
The following IF statement should work but doesn't. I have tested it by components and it appears to function but fails as a whole. What is the correct approach and more importantly why is this logic failing?
Count(
IF(Count({$<[Status]={'OK'}>}[Name])=Count({1<[Status]={'OK'}>}[Name]), // IF Current set = Original set
Count(Distinct{<[Status]={'OK'}>}[Name]), // TRUE = Calculate Level A
Count({<[Status]={'OK'}>}[Event#])) // FALSE = Calculate Level B
)
Result:
All conditions are evaluated as TRUE regardless of whether the current set is equal to the original set or not.
Test A:
Count(
IF(Count({$<[Status]={'OK'}>}[Name])=Count({1<[Status]={'OK'}>}[Name]), // IF Current set = Original set
'Hi Mom', // TRUE = display string Mom
'Hi Dad' // FALSE = display string Dad
) // Chart responds as expected, when drill down changes, displays Mom at level A and Dad at level B.
Test B:
Count(
IF(P=Q, // IF Current set = Original set
Count(Distinct{<[Status]={'OK'}>}[Name]), // TRUE = Calculate Level A
Count({<[Status]={'OK'}>}[Event#])) // FALSE = Calculate Level B
) // P & Q are numbers, if they are set to be equivalent so that the logic is TRUE, measure calculation equals TRUE; if they are set to be different so that the logic is FALSE, measure calculation equals FALSE
Any thoughts on why this is happening? Thanks!