topic Re: Create Table based on columns and measures in DAX Commands and Tips

Create Table based on columns and measures

EvertonRosa — Wed, 15 Mar 2023 23:29:10 GMT

Community, I need a little help!

Is there any way to create a table based on a column and a measure? Let me explain the case:

I have a PRODUCTS table with a column called family_name. Each product has its own family. Ex:

I have a measure that CALCULATE the Time to Win for each opportunitie on table OPPORTUNITIES. Ex:

Time to Win (Family) =

CALCULATE(

AVERAGEX(

SUMMARIZE(

FILTER(fOportunities,fOportunities[statecode] = 1),

fOportunities[opportunityid],

dProducts[family_name],

"AvgDays",MAX(fOportunities[DaystoClose])

[AvgDays]

USERELATIONSHIP(dCalendar[ID Data],fOportunities[actualclosedate])

)

Now, I wloud like to generate a table like this:

I have tried to aproach using SUMMARIZE, ADDCOLUMN but I couldn´t figure out how to do it. Any help?

amitchandak — Thu, 16 Mar 2023 04:48:58 GMT

EvertonRosa , if you create calculated table, the measure can not take slicer value and calculation may not go as planned and will be static

Add a new column, using addcolumns, and use related , relatedtable, or simple max function work across the table

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Anonymous — Thu, 16 Mar 2023 05:16:44 GMT

Create calculate column and use either Related or RelatedTable function.

EvertonRosa — Thu, 16 Mar 2023 17:32:11 GMT

Thanks, amitchandak. I found a solution after watch your videos.