Forum Discussion

JarnoVisser's avatar
JarnoVisser
Helper I
7 years ago
Solved

Summarise table with condition

Hello   I have a transaction table with the following data:   Transaction date Amount Urgency Customer 1-1-2017 50 high a 30-5-2017 100 medium a 30-9-2017 30 low a 1-1-...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    If you follow the Star Schema approach which LivioLanzo showed, then you can also use the below Calculated column HighestUrgencyID in Data table, and create a relationship between Urgency[UrgencyID] & Data[HighestUrgencyID]. This way your measure will be just SUM(Data[Amount]).

     

    HighestUrgencyID =
VAR TransactionYear =
    RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = 'Data'[Customer]
RETURN
    CALCULATE (
        MAX ( 'Data'[UrgencyID] ),
        FILTER (
            'Data',
            'Data'[Customer] = CurrentCustomer
                && RELATED ( 'Calendar'[Year] ) = TransactionYear
        )
    )
AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

If you follow the Star Schema approach which LivioLanzo showed, then you can also use the below Calculated column HighestUrgencyID in Data table, and create a relationship between Urgency[UrgencyID] & Data[HighestUrgencyID]. This way your measure will be just SUM(Data[Amount]).

 

HighestUrgencyID =
VAR TransactionYear =
    RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = 'Data'[Customer]
RETURN
    CALCULATE (
        MAX ( 'Data'[UrgencyID] ),
        FILTER (
            'Data',
            'Data'[Customer] = CurrentCustomer
                && RELATED ( 'Calendar'[Year] ) = TransactionYear
        )
    )
JarnoVisser's avatar
JarnoVisser
Helper I
7 years ago

Thank you all! The most easy one even to verify is the solution of Akhil. The syntax of his solution should only be written as follows:

 

HighestUrgencyID =
VAR TransactionYear = RELATED ( 'Calendar'[Year] )
VAR CurrentCustomer = RELATED ('Dim'[Customer]
RETURN
    CALCULATE (
        MIN ( 'Data'[UrgencyID] ),
        FILTER (
            'Data',
            'Data'[Customer] = CurrentCustomer
                && 'Data'[Year] ) = TransactionYear
        )
    )