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

Dynamic Column calculation based on Slicer Selection

Hello!

 

I have a table called 'employees' that has employee information. This contains a measure called 'link percentile' that takes the total links an employee accomplishes and assigns them a percentile based on their standing in the organization.

 

I have another table called 'leads table' that has data tying employees to their lead sources.

 

The goal is to have a bar graph of counts of sources that can be filtered by the date the link was completed AND the percentile of the employee. I can use a slicer to filter based on the date of the link because it's a static value, but filtering a relative value (top 10% of employees) within that date range is not possible because it's a measure.

The data looks like this:

 

employee table:
Employee ID | Total Links (measure) | Link Rank | Link Percentile
12345                           65                         5                  1.5%
etc.

 

 

leads table:

source  |  employeeID  |  date linked  |  Count
google          12345            1/2/2018       1

etc.

 

 

Total Links =
IF(
    ISBLANK(
        SUM('leads table'[Count])
    ), 0, SUM('leads table'[Count])
)
 
 
Link Rank =
RANKX(
    ALLSELECTED( 'employee table' ),
    [Total Links],,DESC)
 
 
Link Rank Percentile =

Var maxRank =

MAXX (
    ADDCOLUMNS(
        ALLSELECTED( 'employee table' ),
        "Ranks",
        RANKX(
            ALLSELECTED( 'employee table' ),
            [Total Links],,DESC)
    ),
    [Ranks]
)

Return

[Link Rank] / maxRank
 
Thanks in advance for anyone's help!
1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @bice_cold ,

 

As i know the value in a calculated column will not be dynamic. Here we can create measures and create table visual to work on it.

 

Total Links = 
IF(
    ISBLANK(
        SUM('leads table'[Count])
    ), 0, SUM('leads table'[Count])
)
 
link rank = RANKX(ALLSELECTED('leads table'),[Total Links],,DESC)
Link Rank Percentile = var _max =MAXX(ALLSELECTED('leads table'),[link rank])
return
DIVIDE([link rank],_max)

Capture.PNG

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @bice_cold ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
v-frfei-msft
Community Support
Community Support

Hi @bice_cold ,

 

As i know the value in a calculated column will not be dynamic. Here we can create measures and create table visual to work on it.

 

Total Links = 
IF(
    ISBLANK(
        SUM('leads table'[Count])
    ), 0, SUM('leads table'[Count])
)
 
link rank = RANKX(ALLSELECTED('leads table'),[Total Links],,DESC)
Link Rank Percentile = var _max =MAXX(ALLSELECTED('leads table'),[link rank])
return
DIVIDE([link rank],_max)

Capture.PNG

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.