Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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 ID | Open Month | Close Month | Created by |
T1 | Jan | Feb | Employee |
T2 | Jan | March | Client |
T3 | Feb | March | Client |
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 Name | Measure1 | Measure2 |
Jan | sum of tickets that were closed in Jan irrespective of open date | sum of tickets created and closed within Jan |
Feb | sum of tickets that were closed in Feb irrespective of open date | sum of tickets created and closed within Feb |
Mar | sum of tickets that were closed in March irrespective of open date | sum of tickets created and closed within Mar |
Solved! Go to Solution.
@Anonymous The file is attached below my signature:
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
)
@Anonymous The file is attached below my signature:
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
)
User | Count |
---|---|
10 | |
9 | |
7 | |
4 | |
4 |