cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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"

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.

Brgds

1 ACCEPTED SOLUTION
Community Support

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))`

Best Regards

Maggie

4 REPLIES 4
Community Support

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))`

Best Regards

Maggie

Frequent Visitor

Hi Maggie

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

Community Support

Use this for a try

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

Best Regards

Maggie

Frequent Visitor

Hi Maggie,

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

Thanks a lot for your help!

Brgds

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.