How to make most use of Measure instead of Calculated Columns and Summary Tables?

[Anonymous]

I have two tables like below :

GA_Data : 

MUID	DashboardSpecialty	DashboardProfession	EventDTTM	TBID	ParentCategory
6480030	Ophthalmology	PHYSICIAN	2019-06-12 9:18:05	80396	Public Health & Policy
6511955	Anesthesiology	PHYSICIAN	2019-06-12 11:56:51	80243	Opinion
6512095	Psychiatry	PHYSICIAN	2019-06-12 6:49:56	80419	Public Health & Policy

Demand_Data : 

MasterUserID	CompCampaignSpecialty	CompCampaignAdv	CompCampaignDrug	Reporting_Specialty	CompCampaignDateCreated	CompDrugLMkey
6285009	Family Medicine	Merck	Gardasil	Other	2019-05-15	3474
6882206	Family Medicine	MK	Gard	Other	2019-05-15	3474
15666588	Family Medicine	MK	Gard	Other	2019-05-15	3474

GA(google analtyics ) data and Demand_data are two sample tables by using them I am trying to make few calculations for each user of GA_Data. Also trying to keep all this calcuations dynamic as I want to use a Date slicer like below :

Sample View

 Note : M_ = Created by Measure , Col_ = Created by Column

----------------------------------------------------------------------------------------------------------------------------

I have used this steps till now :

Part-1.a) 
Custom_Columns on GA_Data : 
	Col_DateKey = FORMAT(GA_Data[EventDTTM],"DD-MM-YY")
	Col_Hour = HOUR(('GA_Data'[EventDTTM]) )
	Col_AM or PM ? = If(HOUR('GA_Data'[EventDTTM]) < 12 , "AM","PM") 
	Col_DayName = UPPER(FORMAT('GA_Data'[EventDTTM],"DDD"))
	Col_DayNumofMonth = DAY('GA_Data'[EventDTTM])
	Col_WeekDay = WEEKDAY('GA_Data'[EventDTTM])
	Col_WeekNum = WEEKNUM('GA_Data'[EventDTTM])
	Col_Year = YEAR('GA_Data'[EventDTTM])
	Col_MonthNum = MONTH('GA_Data'[EventDTTM])
	Col_MonthNameAbr = UPPER(FORMAT('GA_Data'[EventDTTM],"MMM"))
	Col_Month&Year = CONCATENATE('GA_Data'[Col_MonthNameAbr] & " ",'GA_Data'[Col_Year])
	Col_QuarterNum = ROUNDUP(MONTH('GA_Data'[EventDTTM])/3,0)
	Col_QuarterName = CONCATENATE("Qtr-",'GA_Data'[Col_QuarterNum])

Part-1.b)
Custom_measures & columns on GA_Data & Demand_Data:(Working)
	M_Activity Days= DISTINCTCOUNTNOBLANK(GA_Data[Col_DateKey])
	M_Last Transaction = MAX('GA_Data'[EventDTTM])
	M_Frequency = count('GA_Data'[TBID])
	M_Interest =  DISTINCTCOUNT(GA_Data[ParentCategory])
	M_Recency = DateDiff(MAX('GA_Data'[EventDTTM]),Today(),DAY) 
	M_DemandValue = DISTINCTCOUNT('Demand_data'[CompDrugLMkey]) 
	M_Total_Listcount = COUNT('Demand_data'[CompDrugLMkey])
	M_dV = SWITCH(TRUE(),'GA_Data'[M_Total_Listcount] <,0,'GA_Data'[M_Total_Listcount]<2,1,  'GA_Data'[M_Total_Listcount]<11,2,'GA_Data'[M_Total_Listcount]<41,3,'GA_Data'[M_Total_Listcount]<81,4,'GA_Data'[M_Total_Listcount]<161,5,'GA_Data'[M_Total_Listcount]<321,6,7)

	Col_Boolean = IF(DISTINCTCOUNT('GA_Data'[TBID]) > 0 ,"1","0")  
	Col_Active Hour = MAx(GA_Data[Col_Hour])
	Col_Active Dayofweek = VAR 
		DayNumofWeek = max(GA_Data[Col_WeekDay])
		RETURN 
			SWITCH(
				DayNumofWeek ,
				2 , "Monday" ,
				3 , "Tuesday" ,
				4 , "Wednesday" , 
				5 , "Thursday" ,
				6 , "Friday" ,
				7 , "Saturday" ,
				1,"Sunday"
			)

	--Working
	M_Below30Days? = SWITCH (
    TRUE (),
    DateDiff(MAX('GA_Data'[EventDTTM]),Today(),DAY) < 30 , "Yes", "No"
    )
    
    --Not Working
    M_Below60Days? = SWITCH (
    TRUE (),
    GA_SummaryData[M_Recency] < 60 , "Yes", "No"
    )

-------------------------------------------
Part-2.a)
CustomTables on GA_Data: 
	ct_PerMonthVisits = SUMMARIZE('GA_Data','GA_Data'[MUID],GA_Data[Month&Year], 								"Visits/month",count('GA_Data'[TBID]))
	ct_PerHourVisits = SUMMARIZE('GA_Data', 'GA_Data'[MUID],GA_Data[Col_Hour], 							"Visits/hour",count('GA_Data'[TBID]))

CustomTables on ct_PerMonthVisits:

ct_VisitsCheck = SUMMARIZE(ct_PerMonthVisits, ct_PerMonthVisits[MUID],
"Is5Visits/Month?",IF(count(ct_PerMonthVisits[MUID]) > 5 ,"TRUE","FALSE"), 					"Is10Visits/Month?",IF(count(ct_PerMonthVisits[MUID]) > 10 ,"TRUE","FALSE"))

---------
Part-3.c) not working
M_Segment = SWITCH (
    TRUE (),
    GA_Data[M_Frequency] > 120  && GA_Data[M_Recency] < 30  && RELATED(ct_VisitsCheck[Is10Visits/Month??])== "TRUE" , "HIGH",
    GA_Data[M_Frequency] > 30 && GA_Data[M_Frequency]  && GA_Data[M_Recency] < 60  && LOOKUPVALUE(ct_VisitsCheck[Is5Visits/Month?],ct_VisitsCheck[MUID],0) == "TRUE", "MID",
    "LOW"
    )

----------------

Can anyone help me on below questions :

Q1. How to use a Measure instead of column ? 

--works
Col_Hour = HOUR(('GA_Data'[EventDTTM]))

--not works
M_Hour = HOUR(('GA_Data'[EventDTTM]))

If I convert most custom columns into Measures , I can reduce load on my report as I have over 20-25M rows per year . 

Q2. Is it fine to add  all Measures for Both table into GA_Data table like I have generated M_Recency  and  M_DemandValue meausres within GA_Data table only ? better suggestion? 

Q3. How to generate Col_Below30Days using pre-generated meausre : M_Recency ? 

If I am using replace M_Recency with the orginal stuff its working fine.

M_Below460Days? = SWITCH (
    TRUE (),
    DateDiff(MAX('GA_Data'[EventDTTM]),Today(),DAY) < 460 , "Yes", "No"
    )

Q4. Instead of generating custom_Tables like ct_PerMonthVisits  how can I directly generate ct_VisitsCheck table, is there any way to achive this ?

Q5. I am not able to generate M_Segment  or Col_Segment , I tried to use RELATED() function which will work if I create  separate summary_table for GA_Data . Also thought to use LookUPVALUE(), but its not working.

Data model

 Thanks

[MFelix]

Hi @Anonymous ,

One thing that I see in your model is that you have a many to many relationship between your two tables this is a bad practice since the results will be more complex to achieve since when you select one value on a table you will get multiple result on that table and on the next one so a best practice is to create a dimension table with unique values to make the relationship between both tables.

That will facilitate in the creation of your visualizations and your measures, probably some of the visualizations don't even need to have measures.

Can you tell me what is the column that you use to relate both tables?

If you are abble to share a sample file would be even better. After knowing the relationship between the tables I can try and help you out on the questions you have.

Just to get you going measures are different on calculated columns mainly on the filter level. Columns are calculated at a row level for each row you make a calculation and measures are at an aggregator level. So you can have a column with a simple syntax as Column1 - Column2 while in a measure this needs and aggregator similar to SUM(Column1) - Sum(column2).

Check the link below with a detailed explanation and examples.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Also notice that you also need to analize the columns you have  created because some of them you may need to keep them because it's easier to make the calculations at row level than to make it with a measure and have impact on performance.

Finally believe that you also need to create a date table that will relate both tables also, as a good practice a calendar table is always the best option in Power BI.