cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

Hi Experts,

I am looking Count and Rank based on Single Criteria and also combination of 2 Criteria's.

Please see below Table A and Table B.  I am looking to achieve Result and Rank for Table A and Table B Please.

Table A I would like to acheive Result and Rank Please based on Values Column

Table B, I want to acheive Result and Rank based on Value and ID please

1 ACCEPTED SOLUTION
Impactful Individual

hello @Sudhakar510
i created a sample data

the uncessary column is just an added column so the allexcept later on works, because i would assume you have more than those 2 columns

for table A with only values,use :

``Count of Items alone = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[Value]))``

and for ranking

``````table 1 rank =
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],"__Count",[Count of Items alone])
RETURN MAXX(FILTER(__tmpTable2,Help[Value]=__currentvalue),[__Rank])``````

now for table B:

``count of item per id = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[ID],Help[Value]))``

but if you only have those 2 columns , id and value, use

``count of item per id = CALCULATE(COUNT(Help[Value]))``

for ranking:

``````table 2 rank =
VAR __currentid = MAX(Help[ID])
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],Help[ID],"__Count",[count of item per id])
RETURN MAXX(FILTER(__tmpTable2,AND(Help[Value]=__currentvalue,Help[ID] = __currentid)),[__Rank])``````

if i solved you problem, a kudos is well appreciated as well as mark it as solved

3 REPLIES 3
Helper I

Thank you @eliasayy

Helper I

Thank you very much @eliasayy , I will give a try with your measure and will update you as soon as possible. My network is super slow today but I am on it and will let you know soom as soon as ETL process finishes.

Impactful Individual

hello @Sudhakar510
i created a sample data

the uncessary column is just an added column so the allexcept later on works, because i would assume you have more than those 2 columns

for table A with only values,use :

``Count of Items alone = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[Value]))``

and for ranking

``````table 1 rank =
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],"__Count",[Count of Items alone])
RETURN MAXX(FILTER(__tmpTable2,Help[Value]=__currentvalue),[__Rank])``````

now for table B:

``count of item per id = CALCULATE(COUNT(Help[Value]),ALLEXCEPT(Help,Help[ID],Help[Value]))``

but if you only have those 2 columns , id and value, use

``count of item per id = CALCULATE(COUNT(Help[Value]))``

for ranking:

``````table 2 rank =
VAR __currentid = MAX(Help[ID])
VAR __currentvalue = MAX(Help[Value])
VAR __tmpTable = ALL(Help)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,Help[Value],Help[ID],"__Count",[count of item per id])
RETURN MAXX(FILTER(__tmpTable2,AND(Help[Value]=__currentvalue,Help[ID] = __currentid)),[__Rank])``````

if i solved you problem, a kudos is well appreciated as well as mark it as solved

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors