Find Dax measures instead of calculated tables that returns similar result to improve efficiency

[sivarajan21]

Hi,

I have this below dataset which has calculated columns(Profile,Direct,Target,Accrual kwh,Accrual Source)

PoinTS_id	Data date	Month	Profile	Direct	Target	Accrual kwh	Accrual Source
123	01/04/2022 00:00	Apr			44	44	Target
123	02/04/2022 00:00	Apr	3		44	3	Profile
123	03/04/2022 00:00	Apr			44	44	Target
123	04/04/2022 00:00	Apr			44	44	Target
123	05/04/2022 00:00	Apr			44	44	Target
123	06/04/2022 00:00	Apr			44	44	Target
123	07/04/2022 00:00	Apr			44	44	Target
123	08/04/2022 00:00	Apr	50		44	50	Profile
123	09/04/2022 00:00	Apr	65		44	65	Profile
123	10/04/2022 00:00	Apr	0		44	0	Profile
123	11/04/2022 00:00	Apr	87		44	87	Profile
126	12/04/2022 00:00	Apr		6	33.33333333	6	Direct
126	13/04/2022 00:00	Apr			33.33333333	33.33333333	Target
126	14/04/2022 00:00	Apr			33.33333333	33.33333333	Target
126	15/04/2022 00:00	Apr		8	33.33333333	8	Direct

Please find attached sample dataset in below link:

Missing Calendar.pbix

I am happy that I was able to create this calculated table. But is there any way we can achieve the same result(table) using Dax measures instead of calculated columns. for example, I mean to ask that I have calculated column measure for profile as below:

Profile = CALCULATE (

        SUMX('Table','Table'[Units]),

        FILTER (

          'Table',

            'Table'[Points Id] = 'Missing Calendar date_1'[PoinTS_id]

            && 'Table'[Data Date] = 'Missing Calendar date_1'[Data date]

            && 'Table'[Source] = "Profile"))

Is it possible to write the same above measure as a Dax measure that gives the same result as the profile column gives now as below?

PoinTS_id	Data date	Month	Profile
123	01/04/2022 00:00	Apr
123	02/04/2022 00:00	Apr	3
123	03/04/2022 00:00	Apr
123	04/04/2022 00:00	Apr
123	05/04/2022 00:00	Apr
123	06/04/2022 00:00	Apr
123	07/04/2022 00:00	Apr
123	08/04/2022 00:00	Apr	50
123	09/04/2022 00:00	Apr	65
123	10/04/2022 00:00	Apr	0
123	11/04/2022 00:00	Apr	87
126	12/04/2022 00:00	Apr
126	13/04/2022 00:00	Apr
126	14/04/2022 00:00	Apr
126	15/04/2022 00:00	Apr

Reason I wanted dax measures is to increase efficiency. At present,when i use this calculated table for my large dataset, it gives me 

errors such as 'There's not enough memory to complete this operation. Please try again later when there may be more memory available'

Please let me know if you need further info.

Thanks in advance

@Ahmedx @AlB @lbendlin @grantsamborn @amitchandak @Greg_Deckler 

[lbendlin · Accepted solution]

see if the attached version is any faster.

[Ahmedx · Accepted solution]

pls try this

Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))&""
----
or
Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))+0

[Ahmedx · Accepted solution]

pls try this

Accrual sai kwh = 
       IF([Profile22_all]="",[TargetSai_all],[Profile22_all])

[Ahmedx · Accepted solution]

pls try this

Accrual sai kwh = 
       IF([Profile22_all]="",
       IF([Direct sai_all],[Direct sai_all],[TargetSai_all]),[Profile22_all])

[Ahmedx · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiCKBgsjSzRaR1uHA?e=S9P239

[Ahmedx · Accepted solution]

hi! you can try this

[sivarajan21]

Hi @Ahmedx ,

Many thanks for all this solutions and your patience sir

This solutions works like a gem on large dataset.

I will close this long query and and accept this as a solution.😊

Thank you both and community once again

[sivarajan21]

Hi @Ahmedx ,

Thanks for your quick response sir😊

I am amazed with your skills and its a super solution.

Will use this solution on my large dataset and get back to you with feedback

Thanks in advance

[lbendlin · Accepted solution]

see if the attached version is any faster.

[sivarajan21]

Hi @lbendlin ,

This worked like a charm and you are amazing 😊

The problem of memory issues were eliminated. Just to confirm, you made changes to profile & direct calculated column by adding variables correct?

If so, Wow! spot on! you made my day.  you made it look so easy and many thanks

Before closing this query,If you could help me with the below, that means a lot to me.

I want a measure to return rows (Data date & PoinTS_id) that has blank data(no profile) along with rows that has data. for example in below dataset , Profile was created using calculated column.

PoinTS_id	Data date	Month	Profile
123	01/04/2022 00:00	Apr
123	02/04/2022 00:00	Apr	3
123	03/04/2022 00:00	Apr
123	04/04/2022 00:00	Apr
123	05/04/2022 00:00	Apr
123	06/04/2022 00:00	Apr
123	07/04/2022 00:00	Apr
123	08/04/2022 00:00	Apr	50
123	09/04/2022 00:00	Apr	65
123	10/04/2022 00:00	Apr	0
123	11/04/2022 00:00	Apr	87
126	12/04/2022 00:00	Apr
126	13/04/2022 00:00	Apr
126	14/04/2022 00:00	Apr
126	15/04/2022 00:00	Apr

But is it possible to create a measure to achieve? @Ahmedx gave me this beautiful measure 

Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))

But this returns only rows with profile data(as shown in below screenshot) and not all the dates and points for which profile has blank rows.

The expected output is:

Expected Dax measure will return both rows hightlighed in 2 colors.

Please let me know if you need further info

Thanks in advance!

Dax measure will return rows hightlighed in 2 colors.

[Ahmedx]

pls try this

Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))

[sivarajan21]

Hi @Ahmedx ,

Thanks for your quick response!

This really amazing dax! we are really very close. I tried to use this dax to check and here is my observation:

It returns me all the rows where there are data's: for example as shown in below screenshot

Upto this point measure works great. The measure would return dates that has data & no data.I wanted to return rows (date & point id) that has blank data(no profile) along with rows that has data.

The expected output is:

Dax measure will return rows hightlighed in 2 colors.

Please let me know if you need further info

Thanks in advance

[Ahmedx · Accepted solution]

pls try this

Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))&""
----
or
Profile22 = 
VAR _t1 = MAX('Missing Calendar date_1'[PoinTS_id])
VAR _t2 =MAX('Missing Calendar date_1'[Data date])

RETURN
CALCULATE (
                MAX('Table'[Units]),
        FILTER (ALL(
          'Table'),
            'Table'[Points Id] = _t1
            && 'Table'[Data Date] = _t2
            && 'Table'[Source] = "Profile"))+0

[sivarajan21]

Hi @Ahmedx ,

Thanks for your patience!

Just finished the testing, above dax works really well. Brilliant stuff! 

One last bit requires your help before this query is closed. Below measure is not returning the correct values:

Accrual sai kwh =
COALESCE ( [Profile22_all], [Direct sai_all], [TargetSai_all] )

We want this measure to look through [Profile22_all] column and if there is any value then retrun it.

If [Profile22_all] is blank then look for [Direct sai_all] to return any value if present.

If [Direct sai_all] is blank then look for  [TargetSai_all] to return any value if present.

But in our case, it returns value from  [Profile22_all] only and does not look through [Direct sai_all], [TargetSai_all]  columns. for example look at the below screenshot:

Red colour column(Accrual sai kwh) is the measure we created and this returns profile column values perfectly.

we expect black colour marked rows would return target values in Accrual sai kwh 

similarly green colour marked rows would return Direct values in Accrual sai kwh.

The expected output would be:

Please let me know if you need further info

Thanks in advance

[Ahmedx]

I think this is because of the so-called auto exist
You need to create a calendar table and from there pull the date into the matrix

Share sample pbix file to help you.

[sivarajan21]

Hi @Ahmedx ,

Many thanks for your quick response

Interesting to know the term 'auto exist'.

Apologise for not sharing the pbix file

PFA file in below link

Missing Calendar.pbix

Please can you guide me through this sir?

Please let me know if you need further info

Thanks in advance

[Ahmedx · Accepted solution]

pls try this

Accrual sai kwh = 
       IF([Profile22_all]="",[TargetSai_all],[Profile22_all])

[sivarajan21]

Hi @Ahmedx ,

Thanks for your quick response sir

Thats a brillaint solution, but need a slight modification to include 

'Direct sai_all' also in the result. for example in below screenshot

Above pic shows, for 4/12/2022 we have 6 in 'Direct sai_all' column which is needs to be included in 'Accrual sai kwh_Ahmedx' column. Only when 'Direct sai_all' is blank we need the '

TargetSai_all' values to be included.

Similarly pic shows, for 4/15/2022 we have 8 in 'Direct sai_all' column which is needs to be included in 'Accrual sai kwh_Ahmedx' column. 

Priority goes like this: If 'Profile22_all' is not blank return value, if blank check 'Direct sai_all' is blank or not. if not blank return value else return 'TargetSai_all'(this will not be blank)

Just to confirm, Is it possible to use COALESCE function to achieve above?
'

So expected output will be as follows:

'Shared the updated file in below link:

Missing Calendar.pbix

Please let me know if you need further info

Thanks in advance

[Ahmedx · Accepted solution]

pls try this

Accrual sai kwh = 
       IF([Profile22_all]="",
       IF([Direct sai_all],[Direct sai_all],[TargetSai_all]),[Profile22_all])

[sivarajan21]

Hi @Ahmedx ,

Apologise for delay as I was in testing phase from yesterday.

Thanks for a wonderful solution! and it works like a gem😊. you made my day!

I am going to accept this as solution but before that I expect the total column of this measure to show as 549.67(sumx of all rows of 'Accrual sai kwh_ahmedx_2'.

But currently it shows as below:

Expected total will be:

Please find file is in below link:

Missing Calendar.pbix

Please let me know if you need further info

Thanks in advance

[Ahmedx · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiCKBgsjSzRaR1uHA?e=S9P239

[sivarajan21]

Hi @Ahmedx ,

Apologise,Just saw your message

Many thanks for your quick response  and this works like a charm😊

Wow! spot on! you made my day.  you made it look so easy and many thanks 

You are so fluent with the measures that you made it look so easy. I don't have any words to describe. 

Let me have a look into this measure to test for my large dataset.

Once this is done, I will close this query.

Many thanks guys! seriously brilliant stuff from you both 

@Ahmedx and @lbendlin  god bless you both