Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add the value from one article to other articles

I have a table with Articles that have a combination of two articles. I want to create a new Value Column where I add the value of those articles to the basic one.

Example: The value from article Vitamin C + Vitamin D needs to be added +20 on Vitamin C and +20 on Vitamin D. I'm not sure with is easier in dax or in query.

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    Please refer to attached sample file with the proposed solution based on measure.

    DAX Value = 
SUMX ( 
    VALUES ( 'Table'[Article] ),
    VAR CurrentArticle = 'Table'[Article]
    RETURN
        IF ( 
            CONTAINSSTRING ( CurrentArticle, "+" ),
            0,
            SUMX ( 
                FILTER ( 
                    ALL ( 'Table'[Article] ),
                    CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
                ), 
                VAR ThisArticle = 'Table'[Article]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ) )
            )
        )
)

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 
    Please refer to attached sample file with the proposed solution based on measure.

    DAX Value = 
SUMX ( 
    VALUES ( 'Table'[Article] ),
    VAR CurrentArticle = 'Table'[Article]
    RETURN
        IF ( 
            CONTAINSSTRING ( CurrentArticle, "+" ),
            0,
            SUMX ( 
                FILTER ( 
                    ALL ( 'Table'[Article] ),
                    CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
                ), 
                VAR ThisArticle = 'Table'[Article]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ) )
            )
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you, tamerj1 

      Can you explain the logic behind it? How it knows that an Article that contains + needs to go on the corelated Article and not another? 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 
        It doesn't have to do that. It just need to check whether the current row Article name is part of the name of the article under the iteration inside FILTER.

        The code is pretty simple, just concentrate on the inner SUMX:

        SUMX (
    FILTER (
        ALL ( 'Table'[Article] ),
        CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) )
)

        This is a simple SUMX over a filter of all articles that conatin the name of the current article.

        The if statement is just to return zoro in case "+" is partr of the name (a combined name)

        IF (
    CONTAINSSTRING ( CurrentArticle, "+" ),
    0,
    SUMX (
        FILTER (
            ALL ( 'Table'[Article] ),
            CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
        ),
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )
)

        And finally the outer SUMX is just to provide correct values at total level

        DAX Value =
SUMX (
    VALUES ( 'Table'[Article] ),
    VAR CurrentArticle = 'Table'[Article]
    RETURN
        IF (
            CONTAINSSTRING ( CurrentArticle, "+" ),
            0,
            SUMX (
                FILTER (
                    ALL ( 'Table'[Article] ),
                    CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
                ),
                CALCULATE ( SUM ( 'Table'[Value] ) )
            )
        )
)