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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to get measure value from table B for each items of a table A

I am trying to get value for each month name in TABLE A from values of TABLE B

TABLE A

Month Name
Jan
Feb
Mar

 

TABLE B

Ticket IDOpen MonthClose MonthCreated by
T1JanFebEmployee
T2JanMarchClient
T3FebMarchClient

 

I want RESULT in form of table as below, 

I have achieved the below result by creating columns in TABLE A but unable to change values using slicer of "created by" column available in table B so I am looking to create measure which gets affected by slicer used on table B.

 

Month NameMeasure1Measure2
Jansum of tickets that were closed in Jan irrespective of open datesum of tickets created and closed within Jan
Febsum of tickets that were closed in Feb irrespective of open datesum of tickets created and closed within Feb
Marsum of tickets that were closed in March irrespective of open datesum of tickets created and closed within Mar
1 ACCEPTED SOLUTION
AntrikshSharma
Super User
Super User

@Anonymous The file is attached below my signature:

1.png

M1 = 
SUMX ( 
    VALUES ( TableA[Month Name] ),
    VAR CurrentMonth = TableA[Month Name] // Month from the row context
    VAR ClosedInCurrentMonth = 
        // Irrespective of open date
        CALCULATE ( 
            DISTINCTCOUNT ( TableB[Ticket ID] ), 
            TableB[Close Month] = CurrentMonth,
            REMOVEFILTERS ( TableA )
        )
    RETURN
        ClosedInCurrentMonth + 0
)
M2 = 
SUMX ( 
    VALUES ( TableA[Month Name] ),
    VAR CurrentMonth = TableA[Month Name] // Month from the row context
    VAR ClosedInCurrentMonth = 
        CALCULATE ( 
            DISTINCTCOUNT ( TableB[Ticket ID] ), 
            TableB[Close Month] = CurrentMonth,
            TableB[Open Month] = CurrentMonth,
            REMOVEFILTERS ( TableA )
        )
    RETURN
        ClosedInCurrentMonth + 0
)

View solution in original post

1 REPLY 1
AntrikshSharma
Super User
Super User

@Anonymous The file is attached below my signature:

1.png

M1 = 
SUMX ( 
    VALUES ( TableA[Month Name] ),
    VAR CurrentMonth = TableA[Month Name] // Month from the row context
    VAR ClosedInCurrentMonth = 
        // Irrespective of open date
        CALCULATE ( 
            DISTINCTCOUNT ( TableB[Ticket ID] ), 
            TableB[Close Month] = CurrentMonth,
            REMOVEFILTERS ( TableA )
        )
    RETURN
        ClosedInCurrentMonth + 0
)
M2 = 
SUMX ( 
    VALUES ( TableA[Month Name] ),
    VAR CurrentMonth = TableA[Month Name] // Month from the row context
    VAR ClosedInCurrentMonth = 
        CALCULATE ( 
            DISTINCTCOUNT ( TableB[Ticket ID] ), 
            TableB[Close Month] = CurrentMonth,
            TableB[Open Month] = CurrentMonth,
            REMOVEFILTERS ( TableA )
        )
    RETURN
        ClosedInCurrentMonth + 0
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.