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
jorgeslzr
Frequent Visitor

count until rank category

I have a table with elements that belong to a category. Both the element and the category are on the table viz as shown:

 

[Element]    [Category]

      a                  x

      b                  y

      c                  z

      d                  x

      e                 y

      f                  y

 

but the categories are ranked. I mean:

[Category]    [Level of importance]

      x                             1

      y                             2

      z                             3

 

What I want is to count on the first table, how many elements appear, for its category OR HIGHER. I want this:

 

[Element]   [Category]  [Count]

      a                  x              2    (2 x's)

      b                  y              5   (because there are 3 y's and 2 x's)

      c                  z               6   (3 y's, 2 x's, 1 z)

      d                  x              2

      e                 y               5

      f                  y               5

          

 

I hope I explained myself.

Thanks

 

1 ACCEPTED SOLUTION
JustJan
Responsive Resident
Responsive Resident

Hi@jorgeslzr 

 

I think this should do the trick:

 
Count = 
var _CurrentCat = SELECTEDVALUE(ElemCat[Category])
var x =
CALCULATE(
countrows(ElemCat),
all(ElemCat),
ElemCat[Category] <= _CurrentCat
)

var result = x

return result
 
Jan

View solution in original post

2 REPLIES 2
JustJan
Responsive Resident
Responsive Resident

Hi@jorgeslzr 

 

I think this should do the trick:

 
Count = 
var _CurrentCat = SELECTEDVALUE(ElemCat[Category])
var x =
CALCULATE(
countrows(ElemCat),
all(ElemCat),
ElemCat[Category] <= _CurrentCat
)

var result = x

return result
 
Jan
harshnathani
Community Champion
Community Champion

Hi @jorgeslzr ,

 

Thanks for explaining. But it gets confusing sometime seeing x, y's and z's.

 

Can you share some sample data and expected output ?

 

Regards,

HN

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.

Top Solution Authors