Skip to main content
cancel
Showing results for 
Search instead 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

Reply
scaballerom
Helper I
Helper I

RANKX with SUMMARIZECOLUMNS and Filter

Hi all,

 

Here is a sample of my dataset, where:

- Job: one job can have multiple tasks, and be executed more than once in a day (job timestamp repeated).

- Task: multiple tasks happen within a job.

- Job TimeStamp: date and time the job started. One job can have multiple executions within a day.

- Job Date: date the job started.

- Job Status: can be either G or C.

- Unique Key: concatenation between Job and Job TimeStamp.

scaballerom_0-1658150650347.png

 

Here is a table I constructed using the following measures:

scaballerom_2-1658150882340.png

GCALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")

Distinct Count Unique Key = DISTINCTCOUNTNOBLANK(Table1[Unique Key])

%G = IF([G]>0,[G]/[Distinct Count Unique Key],0)
Average = CALCULATE([Distinct Count Unique Key],
REMOVEFILTERS(Table1[Job]))/CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Job]),REMOVEFILTERS(Table1[Job]))
 
What I want to do a ranking of those jobs that are above average and below average.
The jobs that are above average (Distinct Count Unique Key > Average), I want to create a table that contains the following:
scaballerom_3-1658151183941.png

 

On the other hand, for those below average (Distinct Count Unique Key < Average):

scaballerom_4-1658151205097.png

 

This ranking should be filtered with a slicer by Job Date.


Ranking = 

VAR SummaryTable =
FILTER(
SUMMARIZECOLUMNS(Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),
"pcte_job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")/DISTINCTCOUNTNOBLANK(Table1[Unique Key])),
[job_failed_aborted]>0)

RETURN
RANKX(ALL(SummaryTable[job_name]),[pcte_job_failed_aborted],,DESC)

 

But it is not working.

Could you please help me out?

Thanks a lot!
Best,
S
4 REPLIES 4
scaballerom
Helper I
Helper I

Hi @amitchandak ,

Thank you so much for your response; I tried to do as you mentioned, but got the following error:

Ranking =

VAR SummaryTable =
FILTER(
SUMMARIZE(Table1,Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),
"pcte_job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")/DISTINCTCOUNTNOBLANK(Table1[Unique Key])),
[job_failed_aborted]>0)

RETURN
RANKX(SummaryTable,[pcte_job_failed_aborted],,DESC)

 

Error message: the value for 'pcte_job_failed_aborted' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

@scaballerom , Create two measure like this and try

 

pcte_job_failed_aborted =Divide(CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),DISTINCTCOUNTNOBLANK(Table1[Unique Key]) )

 

Rank =

Rankx(
FILTER(
SUMMARIZE(allselected(Table1),Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")),
[job_failed_aborted]>0) , [pcte_job_failed_aborted] )

Hi @Amit,

 

That seems to work great (see image below).

scaballerom_0-1658160287741.png

However, how would you incorporate the condition about the comparison on the average?

When I incorporate more sample data (see image below):

scaballerom_4-1658161807304.png

 

And use condition: 

Is Above Avg. = IF([Distinct Count Unique Key]>Table1[Average],1,0) to sort whether the distinct values of jobs are above or below average, the ranking seems to not work:
scaballerom_5-1658161844330.png


Could you please help with this issue? If needed, I can provide both the Excel sample data amb PBI sample report.

 

Thanks so much.

Best,

S

 
 
 
 

 

 

amitchandak
Super User
Super User

@scaballerom , Use summarize and when you give a table name use all or allselected

 

example 

summarize(allselected(Table1), <Other columns>)

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors