Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.