cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

 

I have two tables like below :

GA_Data : 

MUIDDashboardSpecialtyDashboardProfessionEventDTTMTBIDParentCategory
6480030OphthalmologyPHYSICIAN2019-06-12 9:18:0580396Public Health & Policy
6511955AnesthesiologyPHYSICIAN2019-06-12 11:56:5180243Opinion
6512095PsychiatryPHYSICIAN2019-06-12 6:49:5680419Public Health & Policy

Demand_Data : 

MasterUserIDCompCampaignSpecialtyCompCampaignAdvCompCampaignDrugReporting_SpecialtyCompCampaignDateCreatedCompDrugLMkey
6285009Family MedicineMerckGardasilOther2019-05-153474
6882206Family MedicineMKGardOther2019-05-153474
15666588Family MedicineMKGardOther2019-05-153474

 

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 ViewSample 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 modelData model

 Thanks

 

 

1 REPLY 1
Super User III
Super User III

Hi @skondi ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors