Creation of DAX measures instead of calculated columns for calculating time speed % categories

[JasserBI]

Hello All,

I am trying to calculate through only DAX Measures the quantity of tickets being responded in 4 categories of speed (Difference between the timestamp of ticket creation minus the timestamp of ticket_start_to_resolve) :

<1 minute, 1 to 5 minutes, 5 to 60 minutes, >1 Hour

Right now it is calculated in the following steps:

1.- Calculated the difference of timestamps from the two factTable timestamps previously described and named that column as "responseSpeed" -> Performed in PowerQuery

2.- Creation of 4 calculated conditional columns from the previous "responseSpeed" calculation and marked each row as 1 if it is categorized as <1 minute, 1 to 5 minutes, 5 to 60 minutes or >1 Hour ->Perfomed too in Power Query

3.- Calculation of measures for totals by category with additional specific filters related to the business thanks to CALCULATE function and FILTERS from other specific columns. ->Those measures are calculated in DAX and they are calculated like CALCULATED(SUM(), filtered(all(columns, columns="value") of the previous calculated columns in Power Query and filtered.

4.- Finally, those 4 measures in DAX are divided by the total quantity of tickets created so that We have the calculation for every % from each category. ->Performed in DAX and examples like: % <1min = [<1min] / [Total numbers of tickets]

So, what is the deal? 

I am trying to integrate steps 1.- and 2.- in DAX Measures too in order to free space for the .pbix size and later on the model in the PowerBI service. Calculated columns are not considered as the space occupied is higher even than the steps performed 

What I have tried until now 🙂

1.- Perform a measurement called SpeedResponse 

SpeedResponse = SUMX(factTable, 'factTable[ticket_response_timestamp] - 'factTable[ticket_creation_timestamp])

2.- (integration of 2.- and 3.- former steps into one Measurement).

<1 min_total = 

//I know the following is not working, but in that way you will see the idea behind calculating the total of rows (tickets) with specific speed so that I can count them and then filter that total ammount with the additional filters later.

         var _lessThan1Minute = COUNTX(factTable,

                                       CALCULATE([SpeedResponse], IF(AND(HOUR([SpeedResponse])=0, MINUTE([SpeedResponse])<1),1,0))

                                       )

         var _lessthan1MinuteFiltered = 

                 CALCULATE(_lessThan1Minute),

                 FILTER(ALL(factTable[ticketOrigin]), ALL(factTable[ticketOrigin]="outsourcing")

return

_lessthan1MinuteFiltered

Thank you so much in advance community!

[JasserBI]

Yes, absolutely agree with you. That's the main purpose once you have columns. To have them with low cardinality. And that's what is achieved right now.

That's why in order to go beyond I want to remove completely the need to have any kind of columns. Because when We talk about calculate measures when needed there is not space occupied.

Thank you for your comments. I will continue looking for the way to calculate that measure without columns in DAX.

[daXtreme]

Hi @JasserBI 

"I am trying to integrate steps 1.- and 2.- in DAX Measures too in order to free space for the .pbix size"

I'm not convinced this is the right approach. The more things you can do in Power Query, the better. To optimize space requirements in a pbix file, you don't generally do what you want to do. You do it in a different way. Use the new tool from www.sqlbi.com called Bravo to show you where you can make changes to your DATA TYPES to save space. Here it is: Bravo for Power BI by SQLBI

You could/should(?) also go to YT and look for vids by The Masters (Alberto Ferrari and Marco Russo) where they show how to do exactly what you want: reduce the size of a model.

[JasserBI]

Hello @daXtreme! Thank you so much for your response. As far as I know, the principles of freeing space in Power BI are:

1.- Creating Measures in DAX.

2.- Calculated columns in Power Query.

3.- Calculates columns in DAX. 

I did not know the existence from that new tool so I will learn and use it. Right now I was analyzing the model with DAX Studio and Vertipaq analyzer.

Anyway, do you know a way to calculate those steps in DAX measures instead of calculated columns in Power Query or DAX?

Thank you very much in advance

[daXtreme]

As I said, I wouldn't do that. The key to reducing the size of a model/table is to have columns with low cardinality and do everything you can to ensure this is the case.