Count nr of cases

[Anonymous]

Hi, 
I'm quite new to Power Bi and need some help..

I have 2 tables. One table with service cases which shows the name of the case and the date. 
The other table contains name of the customer, og membership type and the From-and to-date based on the membership. 

I want to know what kind of membership the person had when the Case was created. 
So it should count and filter based on the from/to-date. 

The sample pbix can be found here
https://drive.google.com/file/d/1DcOF5cljdPW_S_uHDwLRCKf7VpDE9mMB/view?usp=sharing

PS: I did edit the PBIX. 

The desired result should look like this:
Silver - 2
Gold - 1

Since Johns Case A was when he was a Silver-member, and when case B occured he was a Gold Member. 
Alice is a Silver member. 

This is what I get now:

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here the sample file with solution https://www.dropbox.com/t/Hv6SXT7RxExYumwA
I started with creating a new calculated column to cover the gap in the open "Tabell1[To]" date with the last date in the "Tabell2[Date]" column

New To = 
IF ( 
    ISBLANK ( Tabell1[To] ),
    MAX ( Tabell2[Date] ),
    Tabell1[To]
)

Then create a new table

Tabell3 = 
FILTER (
    CROSSJOIN ( 
        SELECTCOLUMNS ( Tabell1, "Name1", Tabell1[Name], "Membership", Tabell1[Membership], "From", Tabell1[From], "To", Tabell1[New To] ),
        SELECTCOLUMNS ( Tabell2, "Name2", Tabell2[Name], "Case Name", Tabell2[Case name], "Date", Tabell2[Date] )
    ),
    [Name1] = [Name2]
        && [Date] >= [From]
            && [Date] <= [To]
)

The measure is simply

Measure = COUNTROWS ( Tabell3 )

[Anonymous]

The solutions look really good. Tried it on my actual dataset, and it's 95% accurate. I just have to do some tweaks. Thank you so much for the help!

[tamerj1]

Hi @Anonymous 
Please use this measure

Measure = 
SUMX ( 
    VALUES ( Tabell1[Membership] ),
    CALCULATE ( COUNTROWS ( Tabell2 ) )
)

You can download the sample file with the solution https://www.dropbox.com/t/YwENCxv5dmbUvYOj

[Anonymous]

@tamerj1 

Thank you for helping out, but this is still not correct. Looking at your solution and it still says
2 bronze
2 gold
3 silver.

The total is correct, but the individual rows are not correct. 

I was thinking if it is possible to have a formula which is something like:
If the date of the case is between the "from/to" columns, then it should count and return the value of the type of the membership. If it makes any sense..
I think I somehow need to look at the date of the case againt the From and To dates.  

[tamerj1]

@Anonymous 
What should be the correct count?

[Anonymous]

The correct amount should be
Gold - 1 (case A). John was a Gold member when his Case A was registered. 

Silver - 2 (Case B and C). John was a Silver member when his Case B was registered. Alice is a Silver member (and thats why she has no "to-date"). 

 

[tamerj1]

@Anonymous
did it work with you or you still need help?

[Anonymous]

@tamerj1 
I still need help. Have you looked at the updated PBI? And the updated text?

[tamerj1]

Not yet. I'll have a look and let you know

[tamerj1]

Hi @Anonymous 

try

New Measure =
SUMX ( VALUES ( Customers[Membership] ), [Old Measure] )

[Anonymous]

Hi @tamerj1 

What is "Old Measure"?
I have no other measures in the attached pbix. 

[tamerj1]

[Count of Case name]