Forum Discussion

xl0911's avatar
xl0911
Helper III
4 years ago
Solved

Product table and subscription table

expected result     Hello,   I have a product table that looks like that:   Product Id       Product Name       1 Product Y 2 Product X 3 Product Z   And I have a Su...
Need Help
Tutorial Requests
  • speedramps's avatar
    speedramps
    4 years ago

    I have updated the solution as requested to only check if the product was still active at the end of the month. (Originally you implied you wanted active anytime of the month) 

    Click here to download a solution 

     

    Now please click thumbs up and accept as solution.
    It is not fair to keep changing to problem description.
    If we the fix the problem then please accept the solution and raise a new ticket if you want to change the problem description.

     

    WasActive =
    // This measure returns a TRUE if the product was still active at the begining of the period
    VAR mindate = MIN('Calendar'[Date])
    VAR maxdate = MAX('Calendar'[Date])
    VAR previousid =
    CALCULATE(
        MAX(Facts[Table Id]),
        ALL(Facts[Status Date]),
        Facts[Status Date] < mindate)
    VAR previousstatus  =
    CALCULATE(
        SELECTEDVALUE(Facts[Status]),
        ALL(Facts),
        Facts[Table Id] = previousid)
    RETURN
    IF(previousstatus = "Active", TRUE())
     
    IsActive =
    // This measure returns
    // a TRUE if the product was set active at the end of the period
    // a FALSE if the product was set not active at the end of the period
    // the previous months valued if it was not set this month
    VAR mindate = MIN('Calendar'[Date])
    VAR maxdate = MAX('Calendar'[Date])
    VAR lastid =
    CALCULATE(
        MAX(Facts[Table Id]),
        ALL(Facts[Status Date]),
        Facts[Status Date] >= mindate && Facts[Status Date] <= maxdate)
    VAR laststatus  =
    CALCULATE(
        SELECTEDVALUE(Facts[Status]),
        ALL(Facts),
        Facts[Table Id] = lastid)
    RETURN
    SWITCH(TRUE(),
    laststatus = "Active", TRUE(),
    laststatus = "Not Active", FALSE(),
    [WasActive]
    )
     
     
    ActiveProducts =
    // This measure counts the number or products that were active

    SUMX(
        VALUES(Products[Product Id]),
        INT([IsActive])
    )
     
tamerj1's avatar
tamerj1
Community Champion
4 years ago

xl0911 
If there is a record with same ID and same Date then there should be a value from a third column that is different. Otherwise it is a dupicated record. But in this case what do you mean by largest ID while we're talking about the same ID same Date?

xl0911's avatar
xl0911
Helper III
4 years ago

Hi tamerj1,

 

As you can see in the screenshot because we have 2 status's with the same date the measure counts it, and of course we need to count the second/largest Id (In the Status History Id field - number 9).

 

I attached link to download this file: https://we.tl/t-jZGBbCS8hW

 

Hope that makes sense, thank you !

  • xl0911's avatar
    xl0911
    Helper III
    4 years ago

    You are right subscription cannot start with Inactive status (also in my real data).

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    xl0911 
    I would also suggest that in case you still have problems, is better to open a new ticket as this one became conjusted with too many replies and already difficult to track the thread order.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    xl0911 

    Yes of course a unique column required. I'm outside office now. I will check once I return 

  • xl0911's avatar
    xl0911
    Helper III
    4 years ago

    Thank you tamerj1, but still it's not working properly, as you can see in the attached screenshot, if in the same date the highest ID is Active, it should be count it (and it's not)

     

  • xl0911's avatar
    xl0911
    Helper III
    4 years ago

    Hi tamerj1, Any new to my last post ?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi xl0911 
    Sorry I was so busy today I didn't have the chance to look at it. Is it possible for the subsription ID to be repeated again and again? This is a big complication of the issue

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    xl0911 
    The subscription cannot start with Inactive status! Do you have the same scenario in your real data?

  • xl0911's avatar
    xl0911
    Helper III
    4 years ago

    In the "StatusHistory" Table the subscription ID can repeat again and again but in the Subscription table is unique (PK).

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    xl0911 
    Ok but it is worth it that you check carefully with your real data and make sure no other issues.