Forum Discussion

lbendlin's avatar
lbendlin
Super User
1 year ago

 

 

There are too many columns in that table visual.  Anytime you see a horizontal scrollbar in a visual you know you have too many columns.

 

Your _minDate filter is not filtering much. You seemingly spend over 4 seconds to recalculate the same SnapDate over 250000 rows.

CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(
                'Sales Orders Snapshots',
                'Sales Orders Snapshots'[Sales Order ID (H)],
                'Sales Orders Snapshots'[Product ID (L)],
                'Sales Orders Snapshots'[Line Creation Sequence Number (L)]
            ),
            "@SnapDate", MIN( 'Sales Orders Snapshots'[SnapDate] )
        ),
        ALLSELECTED()
    )
 
And then you throw ALLEXCEPT, KEEPFILTERS and TREATAS all into a pile
 
VAR _Result =
    CALCULATE(
        MAX( 'Sales Orders Snapshots'[Customer Item Number (L)]),
        ALLEXCEPT(
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)],
            'Sales Orders Snapshots'[Line Creation Sequence Number (L)]
        ),
        KEEPFILTERS( _FilterSnap )
    )
 
I don't understand what you are trying to achieve with that. Maybe you can explain the business intent?
 
As a side note - That file is way too big. Please provide sample data that fully covers your issue- but not more.. Do not include anything not related to the issue. Do not include any sensitive information.
Please show the expected outcome based on the sample data you provided.
  • Mohan128256's avatar
    Mohan128256
    Helper IV
    1 year ago

    lbendlin my heartfelt thanks and appreciate you on spending your personal time on this to help me out here.

     

    Here is what i am trying to do with the measures which i have created.

     

    Ex:- Customer Reference (H) column.

     

    As per the above screenshot,

    I have 30days of data for the salesorderid = 100S1077426, Productid = 594963-W, line creation number = 1.

    Customer Reference (H) New measure is used to calculate the latest value available in the timeperiod which we have filtered using date range slicer.

    In this case, for the max date 18-09-2024, we have customer reference value as 6631649637

     

    Customer Reference (H) Before measure is used to calculate the initial value available in the timeperiod which we have filtered using date range slicer.

    In this case, for the min date 20-08-2024, we have customer reference value as PENDING2

     

    So Customer Reference (H) New should show 6631649637, Customer Reference (H) Beforeshould show PENDING2, 

     

    But if i change the date range to 10-09-2024 to 18-09-2024 then both should show the same value, as the max date = 18-09-2024, min date=10-09-2024 has the same value.

    As you see, the measures which i have created are giving me the right result and they are working fine when i filter out the data with limited records.

    But actually, the table has 100+ millions of records, where I have to show the columns with their New and before values with conditional formatting if there is difference between New and before, hightlit it.

     

    I have limited the data.

    Please check the same file over the drive.

     

    Hope i did gave you the details which you require to look into this.

     

    Let me know if you need anymore details.

     

    Thanks,

    Mohan V.

     

    • Mohan128256's avatar
      Mohan128256
      Helper IV
      1 year ago

      lbendlin this all works fine when I have individual measures to show along with the required columns in a table visual.

      But when i have all the required measures with New and before to show in the table visual along with conditional formatting then the visual takes to load almost 3 min for rendering.

      I request you to look at the actual file and suggest what i can change to make it more effiecient.

       

      Thanks,

      Mohan V.