none
Calculated Column

    Question

  • Hi friends ,

    I HAVE A calculated column as below, I need to undersatand it. I have dax studio but I an unable to understand how to evaluate this expression there.


    if ( 
    CALCULATE(COUNTROWS('loc'), 
    FILTER('loc', 
    'loc' [OSLKEY] = EARLIER('loc' [OSLKEY]) 

    ) > 1, 
    if (CALCULATE(COUNTROWS('loc'), 
    FILTER('loc', 
    'loc' [Partition] >= EARLIER('loc' [Partition])), 
    FILTER('loc', 
    'loc' [OSLKEY] = EARLIER('loc' [OSLKEY])) 
    ) = 1, [OSLKEY], 
    ([OSLKEY]+([PARTITION]/100))*-1), [OSLKEY]) 

    Actually I have many partitons in a cube BASED ON SYSTEM time. Earlier its query was like -"select * from dim.loc where parent_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second"

    But I had a requirement to fetch child data also, so I changed the query as follows - 

    "select * from dim.loc where parent_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second OR CHILD_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second "

    After I made the changes like that, I am getting data for all other columns as expected, but throwing error in the above calculated column. So I need to resolve this.

    Any suggestion/help will be highly appreciated.Thankyou


    sania

    Tuesday, June 5, 2018 9:01 AM

All replies

  • Hi Sania,

    Thanks for your question.

    >>>After I made the changes like that, I am getting data for all other columns as expected, but throwing error in the above calculated column. So I need to resolve this.
    According to the T-SQL queries provided by you, the differences are that you will get more data if you are using the second query. Since you did not change the DAX forumla, you might need to check the added data with the second query.

    >>>I an unable to understand how to evaluate this expression there.
    CALCULATE(COUNTROWS('loc'),
    FILTER('loc',
    'loc' [OSLKEY] = EARLIER('loc' [OSLKEY])
    )
    )
    Above is to get the number of rows with current OSLKEY in table 'loc‘.

    CALCULATE(COUNTROWS('loc'),
    FILTER('loc',
    'loc' [Partition] >= EARLIER('loc' [Partition])),
    FILTER('loc',
    'loc' [OSLKEY] = EARLIER('loc' [OSLKEY]))
    )

    Above is to get the number of rows with OSLKEY equal current OSLKEY and Partition larger or equal current Partition in table 'loc‘.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Thursday, June 7, 2018 10:51 AM
    Wednesday, June 6, 2018 6:46 AM
    Moderator
  • HI Willson,

    You are right. 

    >>"select * from dim.loc where parent_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second".  This query always returns only 1 row.

    >>"select * from dim.loc where parent_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second OR CHILD_date  between trunc(sysdate) +3/24 and trunc(sysdate) +4/24 - interval  '1' second " . This can return any number of rows.

    Innthe case of 1st query even if duplicacy is happening, the DAX formula is doing this calculation and ([OSLKEY]+([PARTITION]/100))*-1) returning data in calculated column. 

    example - 

    location  partition  oslkey      tech

    A              56               1234       msbi --this is a row for location A

    Now i wil update tech column. so I should get new value in tech column and should get only 1 row. But sometimes I am getting 2 rows(duplicates except tech column) with both old and new tech values as below.

     

    location  partition  oslkey          tech

    A              56              -1234.56     msbi --old row got data by ([OSLKEY]+([PARTITION]/100))*-1) 

    A              57               1234          java -- new row. 

    But in case of new query I get many rows. And when data duplicacy is happening, that DAX formula is not working. I need to edit it but unable to understand which part to edit.


    sania

    Friday, June 8, 2018 1:51 PM
  • Hi Sania,

    Thanks for your response.

    It is pretty to hard to answer this kind of question without accessing your underlying database and the SSAS Tabular database.

    >>>But in case of new query I get many rows. And when data duplicacy is happening, that DAX formula is not working. I need to edit it but unable to understand which part to edit.
    For this question, you can create a view to get the desired results in the underlying database, and use that view as the data source for your SSAS Tabular database.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 11, 2018 6:09 AM
    Moderator