Get all hierarchy elements who apply to filter

  • ktflash

    Ten Centuries

    Points: 1045

    Ahoi,

    the following cube elements are relevant:

    [Ticket].[Tickethierarchie]   --> hierarchy which i need to find the relevant elements of

    [Ticket].[Status]                    --> attribut has multiple elements which i need to filter the relevant hierarchy elements

    [Measures].[Aufwand]         --> Measure which is used to filter aswell, > 0.1

     

    What i need to do is find a filter for all [Ticket].[Tickethierarchie] where:

    • Measure is > 0.1 AND [Ticket].[Status]  is in one of these 4
    FILTER(
    {
    [Ticket].[Status].&[Geschlossen]
    ,[Ticket].[Status].&[Gelöst]
    ,[Ticket].[Status].&[Abgelehnt - nicht realisierbar]
    ,[Ticket].[Status].&[Duplikat]

    },[Measures].[Aufwand] > 0.1
    )

    • AND All Tickets where [Ticket].[Status] is any of these
                        {
    [Ticket].[Status].&[Angenommen]
    ,[Ticket].[Status].&[Bewertung]
    ,[Ticket].[Status].&[CAB relevant]
    ,[Ticket].[Status].&[Eingereicht]
    ,[Ticket].[Status].&[Entwurf]
    ,[Ticket].[Status].&[Freigabe]
    ,[Ticket].[Status].&[Implementieren und Testen]
    ,[Ticket].[Status].&[Offen]
    ,[Ticket].[Status].&[Rollout]
    ,[Ticket].[Status].&[Warte auf]
    ,[Ticket].[Status].&[Zu prüfen]
    }

    Basically i want all [Ticket].[Tickethierarchie] elements, except the the combination of where the Measure [Measures].[Aufwand] is 0 and the [Ticket].[Status] is one of these

     

    {

    [Ticket].[Status].&[Geschlossen]

    ,[Ticket].[Status].&[Gelöst]

    ,[Ticket].[Status].&[Abgelehnt - nicht realisierbar]

    ,[Ticket].[Status].&[Duplikat]

    }

     

     

     

    Trying to improve

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • ktflash

    Ten Centuries

    Points: 1045

    Ok, how do i merch these 2 Queries into 1 so i can use it as a column in a dimension as a filter?

    This is 50% of it:

    CREATE MEMBER CURRENTCUBE.[Ticket].[Status].[Relevanter Status]
    AS
    [Ticket].[Status].&[Angenommen] +
    [Ticket].[Status].&[Bewertung] +
    [Ticket].[Status].&[CAB relevant] +
    [Ticket].[Status].&[Eingereicht] +
    [Ticket].[Status].&[Entwurf] +
    [Ticket].[Status].&[Freigabe] +
    [Ticket].[Status].&[Implementieren und Testen] +
    [Ticket].[Status].&[Offen] +
    [Ticket].[Status].&[Rollout] +
    [Ticket].[Status].&[Warte auf] +
    [Ticket].[Status].&[Zu prüfen],
    VISIBLE = 1;

    But i also need this to apply as OR, so either the top condiation or the one below:

    SELECT [Measures].[Aufwand]  ON COLUMNS ,
    filter(
    [Ticket].[Tickethierarchie].Members,
    Aggregate([Ticket].[Status].&[Geschlossen],[Measures].[Aufwand] < 0.1)
    OR
    Aggregate([Ticket].[Status].&[Gelöst],[Measures].[Aufwand] < 0.1)
    OR
    Aggregate([Ticket].[Status].&[Duplikat],[Measures].[Aufwand] < 0.1)
    OR
    Aggregate([Ticket].[Status].&[Abgelehnt - nicht realisierbar],[Measures].[Aufwand] < 0.1)
    ) ON ROWS

    FROM IT

    Certain Status OR (Certain Status AND  Measure is > 0.1)

    Trying to improve

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply