Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |