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
jtownsend21
Responsive Resident
Responsive Resident

Column Based on Values From a Measure

I think this is simple, but I cannot figure it out. 

I have 2 tables. The first table is my User Table (see example below): 


Employee Table.PNG

 

Then I have a Revenue table (see example below): 

Invoice Table.PNG

 

The goal is to have a slicer (or filter) from the employee table such that when I select an employee it will give me the total amount for that employee whether they are the Account Manager or Consultant. For example if I select Tracy, it would produce the following: 

 

Results.PNG

 

I have attempted to use a combination of a measure and a column to accomplish this. 

The measure would always show the selected employee's name when filtered as follows (this works perfectly)

Dynamic Employee Measure = 
IF(
    ISFILTERED('Users Table'[Employee Name]),
    MAX('Users Table'[Employee Name]),
    "No Employee Selected"
)

 

The column would then look for that name in either the Account Manager column or the Consultant column and return that name. The relationship between the Revenue Table and the User Table is based on this column. This one is the one that isn't working. 

Dynamic Employee Column = 
IF(
    OR(
    [Dynamic Employee Measure] = Revenue[Consultant],
    [Dynamic Employee Measure] = Revenue[Account Manager]),
    [Dynamic Employee Measure],
    "No Employee Selected"
)

 

Unfortunately, I can't get it to say anything besides the "No Employee Selected"

Thank you in advance for your help! 

1 ACCEPTED SOLUTION
jtownsend21
Responsive Resident
Responsive Resident

Someone else helped me figure this out. I can't take credit. The key was to remove the relationship and use the following measure. 

Measure =
CALCULATE (
    SUM ( 'Table2'[Amount] ),
    FILTER (
        'Table2',
        'Table2'[Account Manager] = SELECTEDVALUE ( Table1[Employee] )
            || 'Table2'[Consultant] = SELECTEDVALUE ( Table1[Employee] )
    )
)

View solution in original post

5 REPLIES 5
jtownsend21
Responsive Resident
Responsive Resident

Someone else helped me figure this out. I can't take credit. The key was to remove the relationship and use the following measure. 

Measure =
CALCULATE (
    SUM ( 'Table2'[Amount] ),
    FILTER (
        'Table2',
        'Table2'[Account Manager] = SELECTEDVALUE ( Table1[Employee] )
            || 'Table2'[Consultant] = SELECTEDVALUE ( Table1[Employee] )
    )
)
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur This would work if I could restructure the data. Thank you for the help. See the solution below. 

CheenuSing
Community Champion
Community Champion

Hi @jtownsend21 

 

Have you tried creating a relationship on EmployeeId between the two tables using ManageRelationShip under modelling tab in power bi desktop.

 

Create the slicer of Employees from employeetable.

Then create the table visual from the visualisation pane with the columns from the revenue table.

 

This will work.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing This does not help with the OR statement part of the problem. See Solution below. 

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.