Frequency Count

[Saxon10]

I have a two tables are data and report. In data table contain Date, Item and Vendor.

Report table contain item only. (Item stored as text format. Item column data type is mixed – Sometimes number only or number and text together)

In-between two tables the item column are common.

I would like to get the most frequency vendor according to the item based on the data ranges (From April 2020 to April 2021)

Data:

DATE	ITEM	Vendor
22/04/2020	125	A1
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AE
22/04/2020	125	AR
24/04/2020	4568	H1
24/04/2020	4568	H1
24/04/2020	4568	H1
24/04/2020	4568	H1
24/04/2020	4568	H1
24/04/2020	4568	H1
24/04/2020	4568	H3
24/04/2020	4568	H3
24/04/2020	4568	H3
24/04/2020	4568	H3

Report:

ITEM	Vendor FREQUENCY
125	AE
4568	H1

In Excel I am applying the following array formula =IF($F4="","",IFERROR(INDEX(C:C,AGGREGATE(14,6,ROW($A$3:$A$67)/(MAX(FREQUENCY(IF($A$3:$A$67>=$E$2,IF($A$3:$A$67<=$E$3,IF($B$3:$B$67=$F4,IF($C$3:$C$67<>"",MATCH($B$3:$B$67&$C$3:$C$67,$B$3:$B$67&$C$3:$C$67,0))))),ROW($B$3:$B$67)-2))=FREQUENCY(IF($A$3:$A$67>=$E$2,IF($A$3:$A$67<=$E$3,IF($B$3:$B$67=$F4,IF($C$3:$C$67<>"",MATCH($B$3:$B$67&$C$3:$C$67,$B$3:$B$67&$C$3:$C$67,0))))),ROW($B$3:$B$67)-2)),1)),""))

I would like to get the same result in Power BI. I am looking for New calculated column option.

Any advise please.

Excel File:

Here is the excel file for your refence https://www.dropbox.com/scl/fi/c4e55usqn9ej36bum5acp/FREQUENCY-COUNT-31-05.21.xlsx?dl=0&rlkey=i5ntr3...

PBI File,

https://www.dropbox.com/s/a8f66j5w80xyr5h/FREQUENCY%20ND%20COUNT.pbix?dl=0

[Fowmy · Accepted solution]

@Saxon10 

I created a measure, you can add the ITEM column from Report and the measure. Check the attached file. You can use the slicer to filter for any date period.

Freq = 
MAXX(
    TOPN( 
        1 , 
        ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
    ),
    [Vendor]
)

[Fowmy · Accepted solution]

@Saxon10 

I created a measure, you can add the ITEM column from Report and the measure. Check the attached file. You can use the slicer to filter for any date period.

Freq = 
MAXX(
    TOPN( 
        1 , 
        ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
    ),
    [Vendor]
)

[Saxon10]

Your measure solutions working well. 

is that possibile can I get the new calulate column option inculding date range?

can you please advise.

[Fowmy · Accepted solution]

@Saxon10 

Please find below the formula for the calculated Column that will return only for available vendors for April 2020 to April 2021.

Freq Column = 
CALCULATE(
    MAXX(
        TOPN( 
            1 , 
            ADDCOLUMNS(VALUES(DATA[Vendor]),"Count", CALCULATE(COUNT(DATA[Vendor]))),[Count]
        ),
        [Vendor]
    ),
    FILTER(all(DATA[DATE]), DATA[DATE] >= DATE(2020,4,1) && DATA[DATE] <= DATE(2021,4,30) )
)

[Saxon10]

Thanks for your reply and help.

Your solution working well.

[Saxon10]

Thanks for your reply.

Is there any chance can I get the New calculate column instead of measure because based on the result column (Frequency).