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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.