Forum Discussion

cs141005's avatar
cs141005
Regular Visitor
5 years ago
Solved

Retrieve selected values from another table

Hi all,

 

I have two tables: SUM table and BR table (*table BR has no records)

So, i want to fill BR table with some values from SUM table and also make some calculations.

How can i achieve the desired output?

 

 

Need Help
Tips and Tricks
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi cs141005 ,

    You could delete the BR table blank value ,then use the following dax to create new column :

    Jan MTD1 = 
CALCULATE (
    SUM ( 'Sum Table'[Jan] ),
    FILTER (
        ALL ( 'Sum Table' ),
        IF ('BR table'[title] = "Users+New client",
                'Sum Table'[title] = "New users"
                  || 'Sum Table'[title] = "users" ,
            
            IF (
                'Sum Table'[title] = "New users",
            'BR table'[title] = "New clients"  ,
                'Sum Table'[title] = 'BR table'[title]
            )
        )
    )
)

    FEB MTD1 = 
CALCULATE (
    SUM ( 'Sum Table'[Feb] ),
    FILTER (
        ALL ( 'Sum Table' ),
        IF ('BR table'[title] = "Users+New client",
                'Sum Table'[title] = "New users"
                  || 'Sum Table'[title] = "users" ,
            
            IF (
                'Sum Table'[title] = "New users",
            'BR table'[title] = "New clients"  ,
                'Sum Table'[title] = 'BR table'[title]
            )
        )
    )
)

     

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien

     

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    cs141005 

    You need to create  a new table with the following code:

    BR Table = 
UNION(
    'SUM TABLE',
    ROW( "Title", {"New Users + Users"},"Jan", CALCULATE(SUM('SUM TABLE'[Jan]),'SUM TABLE'[Title] IN {"New Users", "Users"})
    ,"Feb", CALCULATE(SUM('SUM TABLE'[Feb]),'SUM TABLE'[Title] IN {"New Users", "Users"}))
)

     



  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi cs141005 ,

    You could delete the BR table blank value ,then use the following dax to create new column :

    Jan MTD1 = 
CALCULATE (
    SUM ( 'Sum Table'[Jan] ),
    FILTER (
        ALL ( 'Sum Table' ),
        IF ('BR table'[title] = "Users+New client",
                'Sum Table'[title] = "New users"
                  || 'Sum Table'[title] = "users" ,
            
            IF (
                'Sum Table'[title] = "New users",
            'BR table'[title] = "New clients"  ,
                'Sum Table'[title] = 'BR table'[title]
            )
        )
    )
)

    FEB MTD1 = 
CALCULATE (
    SUM ( 'Sum Table'[Feb] ),
    FILTER (
        ALL ( 'Sum Table' ),
        IF ('BR table'[title] = "Users+New client",
                'Sum Table'[title] = "New users"
                  || 'Sum Table'[title] = "users" ,
            
            IF (
                'Sum Table'[title] = "New users",
            'BR table'[title] = "New clients"  ,
                'Sum Table'[title] = 'BR table'[title]
            )
        )
    )
)

     

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien