Forum Discussion
Powerbi Find most common value
In that case create a new calculated column and paste that DAX formula into it.
This formula;
Common Supplier =FIRSTNONBLANK(TOPN(1,VALUES(Table'[Item no.]),RANKX(ALL('Table'[Item no.]),[Supplier Count],,ASC)),1)
Add a visual, consider for example a table.
Add the Supplier Column and Common Supplier measure onto it.
You will get the Common Item for the corresponding Supplier.
And the total 1000 rows doesn't mean that there are 1000 suppliers.
Suppliers are repeated.
You can get a count of total suppliers using DISTINCTCOUNT('Table'[Supplier]).
Still if you want to view for only a limited number of rows, then you can filter the 'Supplier' column and include only the rows which you want to see.
Please let me know if you have any further doubts.
Regards,
Sanket Bhagwat
The second column has the item no the third column has the supliers i need so that the data filters doesnt show duplicate supliers and a 4th column appears with the most frequent item no.
In that case you can go to Power Query Editor and remove duplicates.
Home->Transform Data->Right Click on the Column->Remove Duplicates->File->Close and Apply.
Regards,
Sanket Bhagwat
Still how do i show the most frequent value in the data of Power Query cause i wanna use it for later so i somehow wanna have the most reacuring ITEM NO data in my queries.What function do i call in Power Quary Custom Table so i can get the same result that you did.
You cannot use DAX in Power Query Editor.
You have to first remove duplicates using Power Query Editor and then apply the measure in power BI Desktop.