Findin maximum Count of records by date and IDs

[Farnaz_Raj98]

Hi everyone, I'm new in power BI and I want to  achieve Table 3 but I don't know how?

I have 2 tables with data samples like below, in "Host" Table there are host's infos, in "Order" table there are orders that users bought from hosts, I want to know the maximum order count of a host is on which day of week?

Table: Host

ID	Name
1	Jack
2	Lili
3	Hana
4	Steve

Table:Orders

OrderID	HostID	Price	DateName
100	1	2000000	Saturday
200	2	100000	Sunday
300	1	1250000	Saturday
400	3	205000	Wedensday
500	4	200000	Saturday
600	1	100000	Monday
700	3	300000	Wedensday

Result

HostName	MaximumOrderCount	DateName
Jack	2	Saturday
Lili	1	Sunday
Hana	2	Wedensday
Steve	1	Saturday

[Anonymous · Accepted solution]

Hi  @Farnaz_Raj98 ,

Here are the steps you can follow：

1. Create calculated column.

Orders Table:

Countgroup1 =
COUNTX(
    FILTER(ALL(Orders),
    'Orders'[HostID]=EARLIER('Orders'[HostID])&&'Orders'[DateName]=EARLIER('Orders'[DateName]))
,
[HostID])

Countgroup2 =
MAXX(
    FILTER(ALL(Orders),
    'Orders'[HostID]=EARLIER('Orders'[HostID])),[Countgroup1])

Host Table:

MaximumOrderCount =
CALCULATE(
    MAX('Orders'[Countgroup2]),FILTER(ALL(Orders),
'Orders'[HostID]='Host'[ID]))

DateName =
CALCULATE(
    MAX('Orders'[DateName]),
    FILTER(ALL(Orders),
    'Orders'[HostID]='Host'[ID]&&
    'Orders'[Countgroup2]='Host'[MaximumOrderCount]))

2. Create calculated table.

Table3 =
SUMMARIZE(
    'Host',
    'Host'[Name],'Host'[MaximumOrderCount],'Host'[DateName])

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Farnaz_Raj98]

Thanks alot @Anonymous 
It works 🙂

[Farnaz_Raj98]

Unfortunately it doesn't work john, it returns name of hosts with the same days and same Orders like below image

[tamerj1]

Hi @Farnaz_Raj98 
Please use

Maximum Order Count = 
MAXX (
    ADDCOLUMNS (
        VALUES ( Orders[DateName] ),
        "@Coun", CALCULATE ( COUNTROWS ( Orders ) )
    ),
    [@Coun]
)

DateName = 
VAR MaxCount = [Maximum Order Count]
VAR FilteredDateNames =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Orders[DateName] ),
            "@Coun", CALCULATE ( COUNTROWS ( Orders ) )
        ),
        [@Coun] = MaxCount
    )
RETURN
    MAXX ( FilteredDateNames, Orders[DateName] )

[Farnaz_Raj98]

Hey tamerj
my data model is like folliowing image which host is a table that obtained from experience table and hasn't directly relation with orders.

so after the solution you suggested, the result came out with name of hosts but with repeated Date name and order count

[tamerj1]

@Farnaz_Raj98 
which columns are involved in the relationships? Why do you have to use two way relationships?

[Farnaz_Raj98]

 Orders(ExperienceID) and Experience(ID) have relation with the column in parenthesis and Host(HostID) and Experience(HostID),too. 

I used Two way relationships because I thought may be it affects on the result but it didn't and i forgot to change it into one way 

[johnt75]

You can create a couple of measures like

Max Order Count Day = 
var summaryTable = ADDCOLUMNS( VALUES(Orders[DateName]), "@num orders", CALCULATE( COUNTROWS( Orders)))
return CONCATENATEX( TOPN(1, summaryTable, [@num orders]), [DateName], ", ")

 and

Max Order Count = 
var summaryTable = ADDCOLUMNS( VALUES(Orders[DateName]), "@num orders", CALCULATE( COUNTROWS( Orders)))
return MAXX(summaryTable, [@num orders])

In the event that there are multiple days with the same number of orders, the Max Order Count Day measure will return a comma separated list of all the tied days