topic Re: Count nr of cases in DAX Commands and Tips

Count nr of cases

Anonymous — Mon, 21 Mar 2022 15:03:16 GMT

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:

Re: Count nr of cases

tamerj1 — Mon, 21 Mar 2022 14:20:40 GMT

Hi Anonymous

try

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

Re: Count nr of cases

Anonymous — Mon, 21 Mar 2022 14:31:38 GMT

Hi tamerj1

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

Re: Count nr of cases

tamerj1 — Mon, 21 Mar 2022 14:50:10 GMT

[Count of Case name]

Re: Count nr of cases

tamerj1 — Mon, 21 Mar 2022 15:12:04 GMT

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

Re: Count nr of cases

Anonymous — Mon, 28 Mar 2022 07:05:51 GMT

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

Re: Count nr of cases

tamerj1 — Mon, 28 Mar 2022 07:13:23 GMT

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

Re: Count nr of cases

tamerj1 — Mon, 28 Mar 2022 07:32:10 GMT

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

Re: Count nr of cases

Anonymous — Mon, 28 Mar 2022 08:44:06 GMT

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.

Re: Count nr of cases

tamerj1 — Mon, 28 Mar 2022 09:18:34 GMT

Anonymous
What should be the correct count?

Re: Count nr of cases

Anonymous — Mon, 28 Mar 2022 11:06:22 GMT

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").

Re: Count nr of cases

tamerj1 — Wed, 30 Mar 2022 03:59:04 GMT

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 )

Re: Count nr of cases

Anonymous — Wed, 30 Mar 2022 14:09:34 GMT

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!