topic Re: How do we SUMMARIZE table with some lookup ? in DAX Commands and Tips

How do we SUMMARIZE table with some lookup ?

VoltesDev — Wed, 13 Apr 2022 04:18:40 GMT

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

Re: How do we SUMMARIZE table with some lookup ?

amitchandak — Wed, 13 Apr 2022 04:47:02 GMT

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

Re: How do we SUMMARIZE table with some lookup ?

Samarth_18 — Wed, 13 Apr 2022 04:50:09 GMT

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

Re: How do we SUMMARIZE table with some lookup ?

tamerj1 — Wed, 13 Apr 2022 05:13:10 GMT

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] )