Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Girish_123
Helper I
Helper I

how to fetch the most repeated value from two existing different column

I need to fetch a value between two columns like from columns 1 and columns 2 which is most repeated   i need that one. (eg. Suppose
columns 1 columns 2
A.                  A
B.                 B
A.                 C
C.                  A
D.                  B
A.                  B
A                  D

So here most repeated value is A ( A is repeated 6 times ) like this solution I need.

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Girish_123  you can use this measure to find the mode

_Measure = 
VAR _0 =
    UNION (
        SELECTCOLUMNS ( 'Table', "c1", 'Table'[column1] ),
        SELECTCOLUMNS ( 'Table', "c1", 'Table'[column2] )
    )
RETURN
    MINX (
        TOPN (
            1,
            ADDCOLUMNS (
                _0,
                "Frequency", COUNTX ( FILTER ( _0, EARLIER ( [c1] ) = [c1] ), [c1] )
            ),
            [Frequency]
        ),
        [c1]
    )

 

smpa01_0-1637853327197.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@Girish_123  you can use this measure to find the mode

_Measure = 
VAR _0 =
    UNION (
        SELECTCOLUMNS ( 'Table', "c1", 'Table'[column1] ),
        SELECTCOLUMNS ( 'Table', "c1", 'Table'[column2] )
    )
RETURN
    MINX (
        TOPN (
            1,
            ADDCOLUMNS (
                _0,
                "Frequency", COUNTX ( FILTER ( _0, EARLIER ( [c1] ) = [c1] ), [c1] )
            ),
            [Frequency]
        ),
        [c1]
    )

 

smpa01_0-1637853327197.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

I'll try this one thank you so much

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors