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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Count nr of cases

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:

Fezifez_0-1647874892106.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 )

1.png

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

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 )

1.png

Anonymous
Not applicable

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
Super User
Super User

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
Not applicable

@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.  

@Anonymous 
What should be the correct count?

Anonymous
Not applicable

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
Super User
Super User

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

Anonymous
Not applicable

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

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

tamerj1
Super User
Super User

Hi @Anonymous 

try

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

Hi @tamerj1 

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

[Count of Case name]

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.