Covert Calculated column into a Measure

[_Aleksa_]

Hello,

My data model is extremely slow because i have a few calculated collumns that I am now trying to convert to measures. Would you be able to help with that?

Colums calculations:

Count of Claimant E-Mail Populated - All Files = SWITCH(TRUE(),

'Raw Data'[Claimant E-Mail Populated] = "Yes", "1",

"0"

)

Claimant E-Mail Populated = SWITCH(TRUE(),

CONTAINSSTRING('Raw Data'[Claimant_Email],"noemail"), "No",

CONTAINSSTRING('Raw Data'[Claimant_Email],"donotsend"), "No",

'Raw Data'[Claimant_Email] = "", "No",

"Yes"

)

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

Count of Refuse/noEmail = SWITCH(TRUE(),

'Raw Data'[NoEmail/Refused] = "noemail", "1",

'Raw Data'[NoEmail/Refused] = "refused", "1",

"0")

NoEmail/Refused = if(

    SEARCH("noemail",'Raw Data'[Claimant_Email],1,0),

    "NoEmail",

    IF(

     SEARCH("donotsend",'Raw Data'[Claimant_Email],1,0),

     "Refused")

)

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

E-Mails Captured - After First Payment = SWITCH(TRUE(),

'Raw Data'[Email Captured Ind] = "Yes" && 'Raw Data'[EMail captured on or before First Pay Y/N] = "No", "1",

"0"

)

Email Captured Ind = switch(TRUE(),

'Raw Data'[NoEmail/Refused] = "noemail", "No",

'Raw Data'[NoEmail/Refused] = "refused", "No",

'Raw Data'[Count_of_Blank_Email_Field] = 1, "No",

"Yes")

EMail captured on or before First Pay Y/N = SWITCH(TRUE(),

 'Raw Data'[Email Captured Ind] = "Yes" && 'Raw Data'[Email_Address_Initial_Etry_Dte] > 'Raw Data'[First_Indemnity_Payment_Dte - Copy], "No",

 'Raw Data'[Email Captured Ind] = "Yes" && 'Raw Data'[Email_Address_Initial_Etry_Dte] > 0 && 'Raw Data'[First_Indemnity_Payment_Dte - Copy] > 'Raw Data'[Email_Address_Initial_Etry_Dte], "Yes",

 'Raw Data'[Eligible Claim to Survey] ="No", "Not Eligable",

 'Raw Data'[Count_of_Blank_Email_Field] = 1, "Not Captured",

 ""

)

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

E-Mails Captured - Before First Payment = SWITCH(TRUE(),

'Raw Data'[Email Captured Ind] = "Yes" && 'Raw Data'[First_Indemnity_Payment_Dte - Copy] > 'Raw Data'[Email_Address_Initial_Etry_Dte], "1",

'Raw Data'[Email Captured Ind] = "Yes" && 'Raw Data'[First_Indemnity_Payment_Dte - Copy] = 'Raw Data'[Email_Address_Initial_Etry_Dte], "1",

"0"

)

First_Indemnity_Payment_Dte - Copy

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

% Populated by First Pay = CALCULATE(SUM('Raw Data'[E-Mails Captured - Before First Payment])) / CALCULATE(SUM('Raw Data'[Count of Claimant E-Mail Populated - All Files]) + SUM('Raw Data'[Count_of_Blank_Email_Field]) + SUM('Raw Data'[Count of Refuse/noEmail]))

[Greg_Deckler]

@_Aleksa_ - In general, to convert a calculated column to a measure, wrap your column references in some sort of aggregation like MAX, MIN, SUM, etc. There are nuances. Very difficult to do it just from pasting code and no additional information.

If performance is slow, are you referring to the data load times? Because converting from calculated columns to measures will only help data load, if the performance of the report is slow after data load, it will likey get worse, not better using measures instead of calculated columns.

[_Aleksa_]

@Greg_Deckler 

I tried agregating the with COUNT and SUM functions as I want to use them in a table but even tho the measure was correctly carculated it didn't want to show values in any type of a visua (table, matrix) etc. I got an error message when trying to put it in a table.

When talking about slow performance I mean the time it takes to reload the visuals at the page when a filter is applied. The screenshot below is from the Performance Analyzer which for every action says that the "Other" category is the one that takes the longest.

[Greg_Deckler]

@_Aleksa_ - Hmmm, so, again, I would be careful here with converting things to measures as measures tend to slow things down, not speed things up. Unless for some reason you calculated columns are blowing out your data model in some fashion or I don't know, it's hard to determine what exactly is going on. How big is this data model? Are there lots of relationships between tables and the visuals are displaying columns from multiple tables. 

But, I would be careful thinking that measures are going to solve your problem because 9 times out of 10, measures slow things down, they don't speed things up. Of the things I looked at, they all seemed to be flagging rows and such and I don't think you want those as measures.

[_Aleksa_]

@Greg_Deckler  Thank yo uso much for your feedback and timely responses!
The data model is not big at all. It is about 70 colums (which is quite wide yes) and about 50k rows (very small in my openion). I've had much wider and bigger models that performed much faster. Also, there are no relations whatsoever, it is just one table and these calculations. I am using a Smart Filer  that I haven't used before so I am starting to wonder if the filter itself has something to do with the delay?

[Greg_Deckler]

@_Aleksa_ - Can you elaborate on Smart Filter? Sorry, that's not clicking.

Also, a star schema versus a single wide, flat fact table will be more performant.

[_Aleksa_]

@Greg_Deckler  Here is a link to the Smart Filter by OKViz.

I will look into the star schema, thanks.

[v-xuding-msft]

Hi @_Aleksa_ ,

Have you resolved this issue? If yes, please share your solution. Others will benefit from this thread.  If not, can you please share some sample and your expected results with us? I have viewed your descriptions and your replies. I'm still not clear about your scenario.