Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
xl0911
Helper III
Helper III

Product table and subscription table

2022-08-01_15h19_30.png

expected result

 

 

Hello,

 

I have a product table that looks like that:

 

Product Id      Product Name      
1Product Y
2Product X
3Product Z

 

And I have a Subscription Product Status table that a product can be in Status "Active" or "Not Active".

 

Table Id (Unique)      Product Id       Status              Status Date              
11Active20/01/2018
21Not Active23/01/2018
32Active28/01/2018
43Active14/05/2018
51Active06/03/2019
61Not Active12/08/2019
73Not Active24/02/2020
81Active06/06/2020
92Not Active22/12/2021
103Active02/03/2022

 

As you can see this Subscription Status Table holds the historical status of every product, and every row represents a status change.

 

My goal is eventually to create a visual that shows Every month the number of Active products that I had.
I need a formula to create a measure to achieve this goal.


 Thank you

2 ACCEPTED SOLUTIONS

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

View solution in original post

Hi @xl0911 
I don't understand what is the problem. Here is your sample file with the very same code

https://we.tl/t-dz8qyjAr2T

 

View solution in original post

46 REPLIES 46
speedramps
Community Champion
Community Champion

 @xl0911  I have accepted my solution because I proivided  2 solutions that worked but you keep changing the problem description.

 

In future when you raise a problem, please show your thanks to volunteers who help with a thumb click and accept the solution.  One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

 

 

tamerj1
Super User
Super User

Hi @xl0911 
This version is working perfectly in the sample file. Please download the sample file and compare with yours. https://we.tl/t-c14Wj8tpax

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR PeriodEnadDate = 
            MAX ( 'Date'[Date] )
        VAR TableBeforePeriodEnd = 
            FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
        VAR PreviousActiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
        VAR LastActivation = 
            COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR PreviousIactctiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR Result =
            IF ( 
                PeriodEnadDate >= LastActivation && PeriodEnadDate <= LastInactivation,
                1
            )
        RETURN
            COALESCE ( Result, 0 ),
        CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
    )
)

 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I think your solution is not what I meant because Product Id 1 is not active in the end of the month so under product Id 1 in jan-2018 the count should be 0.

tamerj1
Super User
Super User

Hi @xl0911 
Here is the updated solution with active relationship with the date table and fixed the bug noticed by @speedramps 

https://we.tl/t-NlQUK1FT2T

1.png

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR PeriodStartDate = 
            MIN ( 'Date'[Date] )
        VAR PeriodEnadDate = 
            MAX ( 'Date'[Date] )
        VAR TableBeforePeriodEnd = 
            FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
        VAR PreviousActiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
        VAR LastActivation = 
            COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR TableBeforePeriodStart = 
            FILTER ( Subscription, Subscription[Status Date] < PeriodStartDate )
        VAR PreviousIactctiveTable = 
            FILTER ( TableBeforePeriodStart, Subscription[Status] <> "Active" )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR Result =
            IF ( 
                PeriodEnadDate >= LastActivation && PeriodStartDate <= LastInactivation,
                1
            )
        RETURN
            Result,
        CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
    )
)

I wanted to upload more screenshots but seems I reached my maximum allowable limit of uploaded images!

Thank you.

I'm getting this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

I guess that maybe the formula can't handle a scenario that maybe a product has 2 rows of status in the same date... ? 

 

 

@xl0911 
Can you please pase a screenshot of the measure code?

Found my Mistak..  

@xl0911 
Please check the latest version of the code and the sample file as I amended as per your reply to @speedramps 

I checked your file and it's not the result I wanted.

I attached a screenshot of the result I want.

 

2022-08-01_16h50_50.png

@xl0911 

Why is that? Jan should be 1 right as ID 2 is active. What exactly is your business logic?

Product ID 1 at Jan 2018 should be 0 because in the end this month the product wat "Not Active".

Product ID 1 at March 2019 return to be 1 until the product was "Not Active" at August 2019.

 

Hope it make sense..

@xl0911 
That is exactly what the code is doing. But also Product ID2 is active in Jan therefore it is counted this is why you see 1

This is a screenshot of your attached file, as you can see you have 2 at Jan 2018

2022-08-01_17h56_41.png

@xl0911 
This is not the last file. I shared one more solution after that. Here you go https://we.tl/t-17UszDTihZ

# Active Subscriptions = 
SUMX (
    VALUES ( Products[Product Id] ),
    CALCULATE (
        VAR PeriodEnadDate = 
            MAX ( 'Date'[Date] )
        VAR TableBeforePeriodEnd = 
            FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
        VAR PreviousActiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
        VAR LastActivation = 
            COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR PreviousIactctiveTable = 
            FILTER ( TableBeforePeriodEnd, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
        VAR LastInactivation = 
            COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
        VAR Result =
            IF ( 
                PeriodEnadDate >= LastActivation && PeriodEnadDate <= LastInactivation,
                1
            )
        RETURN
            COALESCE ( Result, 0 ),
        CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
    )
)

Your solution is perfect BUT its not what I need.

Unfortunately I did a big mistake in my simulation and I need to add one more table (you see, any customer can subscribe to any product ID so I miss the sales table between the product table and the status history table).

Now I don't know what to do, should I open a new question or continue from here, what do you suggest ? 

thanks for not giving up 🙂

@xl0911 
Thats fine. Please add all relevant information and sample data including the relevant part of your data model and relastionships 

I attached a file link:  https://we.tl/t-2umLpQQ50q

Hi @xl0911 
I don't understand what is the problem. Here is your sample file with the very same code

https://we.tl/t-dz8qyjAr2T

 

I found A small problem in your solution.

If there is the same date with the same Product Id more then one time its not "tie breaker" with the largest ID.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.