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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fixed Max of Distinct Count in a table

I have a data like this

1.JPG

I have created a measure count# = distinctcount(#Req)

2.JPG

Now how can i achieve max count # ?

 

I have tried

calculated(maxx(table,count#),allexcept(table,Item))

 

i just couldt get the result.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous 

Assuming the second image you show is a table/matrix visual, you can create a measure like this:

 

max count # =
MAXX ( ADDCOLUMNS ( ALL ( Table1[Item] ), "_Res", [count#] ), [_Res] )

 

where [count#] is the measure you already have:

 

count# = DISTINCTCOUNT(Table1[#Req])

and Table1 is the first table you show.

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @Anonymous 

Assuming the second image you show is a table/matrix visual, you can create a measure like this:

 

max count # =
MAXX ( ADDCOLUMNS ( ALL ( Table1[Item] ), "_Res", [count#] ), [_Res] )

 

where [count#] is the measure you already have:

 

count# = DISTINCTCOUNT(Table1[#Req])

and Table1 is the first table you show.

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

Anonymous
Not applicable

Hi Thanks, it works perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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