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

Delay code analysis

Hi all,

 

This should be a pretty easy problem but I´m not able to solve it...

 

I created below table "RFE Trucks"

 

 

table.PNG

What I want to do is analyse the DelayCode1 and DelayCode2 columns

I´m trying to show this in a visual with some sort of rank of those codes (34 in above example). Column contains a number.

 

This is how I imagine the formula:

Check each row and tell me how many times did we use this code and then give me a rank with top used delaycode etc.

Delaycode is optional so there might be a lot of empty cells.

 

Hoping for your help!

 

Brgds

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @TimonMeyer

Based on your requirement, I make a test with my example data.

Create calculated columns

countrows1 = CALCULATE(COUNT(Sheet1[delay1]),ALLEXCEPT(Sheet1,Sheet1[delay1]))
countrows2 = CALCULATE(COUNT(Sheet1[delay2]),ALLEXCEPT(Sheet1,Sheet1[delay2])) 
rank for delay1 = IF([countrows1]<>BLANK(),RANKX(ALL(Sheet1),[countrows1],,DESC,Dense))
rank for delay2 = IF([countrows2]<>BLANK(),RANKX(ALL(Sheet1),[countrows2],,DESC,Dense))

7.png

8.png

Best Regards

Maggie

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @TimonMeyer

Based on your requirement, I make a test with my example data.

Create calculated columns

countrows1 = CALCULATE(COUNT(Sheet1[delay1]),ALLEXCEPT(Sheet1,Sheet1[delay1]))
countrows2 = CALCULATE(COUNT(Sheet1[delay2]),ALLEXCEPT(Sheet1,Sheet1[delay2])) 
rank for delay1 = IF([countrows1]<>BLANK(),RANKX(ALL(Sheet1),[countrows1],,DESC,Dense))
rank for delay2 = IF([countrows2]<>BLANK(),RANKX(ALL(Sheet1),[countrows2],,DESC,Dense))

7.png

8.png

Best Regards

Maggie

Hi Maggie

 

Thanks for your reply...very helpful.

 

Unfortunately I´m having problems with the rank delay.

 

Rankdelay 1 = IF([countdelay]<>BLANK();RANKX(ALL('RFE Trucks';[countdelay];;DESC;Dense))

 It keeps telling me that "Argument ´3´ in ALL function is required". RFE Trucks is my table.

 

 

Where is my mistake here?

 

Brgds

Hi @TimonMeyer

Use this for a try

Rankdelay 1 = IF([countdelay]<>BLANK();RANKX(ALL('RFE Trucks');[countdelay];;DESC;Dense))

Best Regards

Maggie

Hi Maggie,

 

tried that too myself without success...anyway your formula worked now 🙂

 

Thanks a lot for your help!

 

Brgds

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.