How do we SUMMARIZE table with some lookup ?

[VoltesDev]

Hi guys,

I am wondering how to use DAX to summarize a table, but with a lookup by one of its summarized column ?

I have table and data like this :

And I want to summarize the 2nd table but with addition column of the step name getting from 1st table. So to be like this :

I believe I can use Lookup function, but I don't know the correct way of doing it. Anyone can teach me how to ?

Thanks

[tamerj1 · Accepted solution]

Hi @VoltesDev 
If you want with DAX you can use

Table3 = 
SELECTCOLUMNS (
    FILTER (  
        CROSSJOIN (  
            SELECTCOLUMNS ( Table1, "Code", Table1[Statecode], "Name", Table1[Name] ),
            DISTINCT ( Table2 ) 
        ),
        [Statecode] = [Code]
    ),
    "Statecode", [Statecode],
    "Name", [Name],
    "Step", [Step]
)

[Samarth_18]

Hi @VoltesDev ,

Please create a column as below:-

Name = LOOKUPVALUE('Table (2)'[Name],'Table (2)'[statecode],'Table'[Statecode])

Now drag all your values on visual as below:-

or if you need a new summerized table then you could use below formula:-

Table 2 = SUMMARIZE('Table','Table'[Step],'Table'[Statecode],'Table'[Name])

Thanks,

Samarth

[amitchandak]

@VoltesDev , Merge in power bi and remove duplicate

Append and merge  : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Remove Empty and Remove duplicate Rows (Power Query) : https://youtu.be/Hc5bIXkpGVE

Or user Group by 

https://docs.microsoft.com/en-us/power-query/group-by