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

    On the fly Metrics

    Lathaa Vishwanathan

      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/20181
      02/05/20182
      03/05/20183
      04/05/20184
      05/05/20185
      06/05/20186
      07/05/20187
      08/05/20188
      09/05/20189
      10/05/201810
      11/05/201811
      12/05/201812
      13/05/201813
      14/05/201814
      15/05/201815
      16/05/201816
      17/05/201817
      18/05/201818
      19/05/201819
      20/05/201820
      21/05/201821
      22/05/201822
      23/05/201823
      24/05/201824
      25/05/201825
      26/05/201826
      27/05/201827
      28/05/201828
      29/05/201829
      30/05/201830

       

      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
          Ombir Rathee

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

          • 2. Re: On the fly Metrics
            iswarya g

            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
              Lathaa Vishwanathan

              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

               

              DaysEarned Exposure
              10.03
              20.07
              30.07
              40.07
              50.07
              60.07
              70.07
              80.07
              90.07
              100.07
              110.07
              120.07
              130.07
              140.07
              150.07
              160.07
              170.07
              180.07
              190.07
              200.07
              210.17
              220.17
              230.17
              240.17
              250.17
              260.17
              270.17
              280.17
              290.17
              300.17
              • 4. Re: On the fly Metrics
                Ombir Rathee

                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
                  Lathaa Vishwanathan

                  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
                    Ombir Rathee

                    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
                      Ombir Rathee

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

                       

                      • 8. Re: On the fly Metrics
                        Lathaa Vishwanathan

                        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
                          Ombir Rathee

                          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
                            Lathaa Vishwanathan

                            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
                              Ombir Rathee

                              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
                                Lathaa Vishwanathan

                                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) :