Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Your measure solutions working well.
is that possibile can I get the new calulate column option inculding date range?
can you please advise.
@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) )
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for your reply and help.
Your solution working well.
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).
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
58 | |
55 | |
36 | |
33 |
User | Count |
---|---|
79 | |
66 | |
45 | |
45 | |
43 |