12 Replies Latest reply on Nov 20, 2018 11:52 PM by Lathaa Vishwanathan

# On the fly Metrics

Hi all,

I need you help to convert the DAX formulae that I have used in Excel power view to Tableau Measures. I tried but couldn't achieve same results.

I have two tables:

Table A

ProductName      ManufacturingDate    ExpiryDate    Price

Juice                     01/05/2018                 30/05/2018     30

Vegetables           21/05/2018                 30/05/2018     50

Juice Set 10         02/05/2018                  30/05/2018    10

Table B

Date               Day

 01/05/2018 1 02/05/2018 2 03/05/2018 3 04/05/2018 4 05/05/2018 5 06/05/2018 6 07/05/2018 7 08/05/2018 8 09/05/2018 9 10/05/2018 10 11/05/2018 11 12/05/2018 12 13/05/2018 13 14/05/2018 14 15/05/2018 15 16/05/2018 16 17/05/2018 17 18/05/2018 18 19/05/2018 19 20/05/2018 20 21/05/2018 21 22/05/2018 22 23/05/2018 23 24/05/2018 24 25/05/2018 25 26/05/2018 26 27/05/2018 27 28/05/2018 28 29/05/2018 29 30/05/2018 30

to calculate the Earned exposure by each day:

=SUMX (

SUMMARIZE (

'Table A',

'Table A'[ManufacturingDate],

'Table A'[ManufacturingDate],

"Earned Exposure",

COUNTA('Table A'[ProductName] )

* COUNTROWS (

CALCULATETABLE ('Table B',

KEEPFILTERS(

DATESBETWEEN (

'Table B'[Date], 'Table A'[ManufacturingDate], 'Table A'[ManufacturingDate]

)

)

)

)

/ COUNTROWS (

DATESBETWEEN (

'Table B'[Date], 'Table A'[ManufacturingDate], 'Table A'[ManufacturingDate]

)

)

),

[Earned Exposure]

)

How should I rework to achieve the same in Tableau. Much appreciated your help.

• ###### 1. Re: On the fly Metrics

Instead of DAX formula, what is your expected output with the help of these two tables ?

• ###### 2. Re: On the fly Metrics

Hi Lathaa,

Can you share the result of above query in table format(result expected in tableau)..it will be easy to understand

• ###### 3. Re: On the fly Metrics

Expected output:

The below is the output is expected. It should be also aggregate (SUM) by year and month.

Calculation Logic: Day 1 is active only on product juice = 1/ (Expiry date- Manufacturing date +1)  = 1/30 = 0.03

Day 2 is active on two products = 2 / ((Expiry date- Manufacturing date +1) on Product Juice + (Expiry date- Manufacturing date +1) on Product C) = 2/ 59 = 0.07

it remains same till day 21 later all three are active = 3/ ((Expiry date- Manufacturing date +1) on Product Juice +(Expiry date- Manufacturing date +1) on Product B)+ (Expiry date- Manufacturing date +1) on Product C) = 3/69 = 0.17

Needed to be aggregate (SUM) by year and month ie Year 2018 =3 and Month 5 =3 in this case

 Days Earned Exposure 1 0.03 2 0.07 3 0.07 4 0.07 5 0.07 6 0.07 7 0.07 8 0.07 9 0.07 10 0.07 11 0.07 12 0.07 13 0.07 14 0.07 15 0.07 16 0.07 17 0.07 18 0.07 19 0.07 20 0.07 21 0.17 22 0.17 23 0.17 24 0.17 25 0.17 26 0.17 27 0.17 28 0.17 29 0.17 30 0.17
• ###### 4. Re: On the fly Metrics

You need only these two columns in your output ie. Days, Earned Exposure.

and

What do you mean by this ?

"Needed to be aggregate (SUM) by year and month ie Year 2018 =3 and Month 5 =3 in this case"

• ###### 5. Re: On the fly Metrics

I mean, the Exposure calculated in Days should also be aggregated by Year & Month.

For example If I use Year = SUM(all day exposure) on the year

If I use Month = SUM(all day exposure) on the month

So on the given dataset the Exposure portion on Year = 3 and Exposure portion on Month = 3

Hope it gives clear idea.

• ###### 6. Re: On the fly Metrics

Create two Calculated fields:

Days Diff:

DATEDIFF('day',[Manufacturing Date],[Expiry Date])+1

Earned Exposure:

{FIXED [Day]:

SUM(IF [Day]=1 THEN

IF [Product Name]='Juice' THEN 1/[Days Diff] END

ELSEIF [Day]>=2 AND [Day]<21 THEN

IF [Product Name]='Juice' or [Product Name]='Juice Set 10' THEN 1/[Days Diff] END

ELSE 1/[Days Diff]

END

)

}

• ###### 7. Re: On the fly Metrics

Forget to mention that you also need to perform Cross join between two tables like below:

• ###### 8. Re: On the fly Metrics

Hi Ombir rathee,

Thanks for the code. What if I have data for  3 years (2018 -2020) - all months too ? Because I see there was hard code value as passed in metric calculation (Earned Exposure).

Was there any optimized way of computing this logic?

Much appreciated your work. Please share me if there any better way of computing this measure. Thanks

Regards

Lathaa

• ###### 9. Re: On the fly Metrics

What problem are you facing with above solution. After cross join, If one month contains 90 rows then 36 months will contain 3240 rows which isn't a huge dataset.

• ###### 10. Re: On the fly Metrics

If you see the granularity in the time dimension is on Day not at Month level.

According to your assumption, If one month contains 90 rows then the resulted data volume would be 90*365*3 then expected 100,000 rows.

The real dataset I have, had more than 40K transactions per month then the expected growth would be 43M which is very huge thou.

Hope it gives clear background! Kindly let me know if you have any work-around solution to avoid growing data size.

• ###### 11. Re: On the fly Metrics

If you've data in two tables like you shared then cross join is required to get the desired output which ultimately increase the rows count. I can't think of any other way. The DAX you shared also doesn't contains the logic of Day1, Day2 and Day21. I think it is implemented in another Dax. You should found the cross join logic in DAX also.

Because I see there was hard code value as passed in metric calculation (Earned Exposure).

I don't understand this. Which value is hardcoded ?

Could you please post another dataset which looks similar to your real data for multiple months/years so that I can test for performance.

• ###### 12. Re: On the fly Metrics

Hi Ombir Rathee,

Ideally I didn't use any other DAX calculations to devise logic separately Day 1, Day 2 and Day 21. Attached is the excel sample report and you can see the DAX calculations in Power pivot (Ribbon) :