August 19, 2016 at 6:52 am
Is it possible to make a Calculated Measure depending what the user is filtering?
My challenge is, that I have to find out a level on a department (siglum) that the user is filtering on the dashboard later (here in the Cube browser as seen on the picture attached).
But I do not know how to actually figure out in the Calculated Measure what the user used as a criteria. I guess this is hard do find out right?
The goal is to find out if Silgum "ASO" (which is the criteria) is actually inside the "Siglum Internal" or not.
In this case I should count all except the "ASDKG" (marked yellow) which is not in "ASO".
But of course if the user changes the Filter to "ASB", then the Calculated Measure should change. I tried so many thinks like days, but I couldn't it figure it out. I guess I'm missing something. It should be possible I guess somehow right?
Thank you so much for your help in advanced.
Br Simon
------------------------------------------------------------------------------------------------------------------------------------------------
Im working as a Business Intelligence Specialist and mainly with Microsoft BI and data warehousing but also with Oracle Databases. I like working with the data and it is my passion to get more out of the data.
August 19, 2016 at 8:54 am
I think it should be possible to achieve what you need...
You could use a case statement in the calculated measure, using the "currentmember" and "membervalue" functions to perform different calculations based on the current member being returned. Something like the below might work as your calculated measure:
with member [Measures].[Test]
as
(
case [Siglum].[Siglum-Hierarchy].[Siglum].currentmember.membervalue
when 'ASO' then 1
else 0
end
)
August 22, 2016 at 1:14 am
Thank you for your reply. What should I do if "ASO" shouldn't be hard coded and should be Dimension member comparison like is this same as "Siglum Internal"? I tried so many things, but nothing worked as expected.
For example if I tried with the "Siglum Internal" then one problem was, that I could only count if the "Siglum internal" was shown on the result table. But what if I want to count them on the lowest level BUT also want to aggregate on a higher level like only year without any siglum? Then it didn't work any longer..
Hope to hear from you guys again, thank you so much in advance
------------------------------------------------------------------------------------------------------------------------------------------------
Im working as a Business Intelligence Specialist and mainly with Microsoft BI and data warehousing but also with Oracle Databases. I like working with the data and it is my passion to get more out of the data.
August 22, 2016 at 8:34 am
sspaeti (8/22/2016)
For example if I tried with the "Siglum Internal" then one problem was, that I could only count if the "Siglum internal" was shown on the result table. But what if I want to count them on the lowest level BUT also want to aggregate on a higher level like only year without any siglum? Then it didn't work any longer..
Correct...if the "Siglum Internal" field is not part of the result set, then the currentmember() function would return the "All" member. If you think about this it makes logical sense, because your query isn't "stepping down" to any member level within that dimension attribute.
If you still want to do that, use the Descendants() function to "step down" to a specific level within the dimension hierarchy. See reference here: https://msdn.microsoft.com/en-us/library/ms146075.aspx
August 26, 2016 at 1:43 am
I could solve the problem now with this calculated measure:
CREATE
MEMBER CURRENTCUBE.[Measures].[Leavers - inside selected siglum] AS
(
STRTOMEMBER(
"[Siglum CommOut Internal].[Siglum-Hierarchy].&[" + [Siglum].[Siglum-Hierarchy].CurrentMember.Member_Caption + "]"
) , [Siglum].[Siglum-Hierarchy].CurrentMember
,[Measures].[Leavers - All]
)
,VISIBLE = 1
,DISPLAY_FOLDER = 'KPIs' ;
In the end it was a very easy MDX, but I tried many things to get to this easy solution (INSTR, Ancestor, Decedants, ..).
The trick was to add the same hierarchy (Conformed dimensions) twice, once to the siglum (department) for the filter and once for the new siglum where they move to.
What it does is now, if the user selects a siglum from the [Siglum].[Siglum-Hierarchy], then it automatically filter also the other one and I only count the once are not yellow (on my picture in the initial post).
Thank for your help @SSChasing Mays
Simon
------------------------------------------------------------------------------------------------------------------------------------------------
Im working as a Business Intelligence Specialist and mainly with Microsoft BI and data warehousing but also with Oracle Databases. I like working with the data and it is my passion to get more out of the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply