Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
So i have the following measures:
Current Month - Selects the Cost Center for the Currently Selected Month
Current Month =
var FileDate = SELECTEDVALUE(Dates[File Name])
return
IF(ISBLANK((CALCULATE(SELECTEDVALUE(Workday[Cost Center - ID]), Workday[File Name] = FileDate))), "Exit", CALCULATE(SELECTEDVALUE(Workday[Cost Center - ID]), Workday[File Name] = FileDate ))
Previous Month Cost Center - Calculated Column that gets the prior months Cost Center for each row
Previous Month Cost Center =
VAR __prevDate =
CALCULATE(
MAX( Workday[File Name] ),
FILTER(
ALLEXCEPT( 'Workday', Workday[Associate ID] ),
'Workday'[File Name] < MAX( Workday[File Name] )
)
)
RETURN
CALCULATE( MAX( Workday[Cost Center - ID]), ALLEXCEPT( 'Workday', Workday[Associate ID] ), Workday[File Name] = __prevDate )
Transfer Check - Checks if the Prior and Current Months Cost Centers have changed
TransferCheck = IF(AND([Current Month] <> [Prev], AND([Current Month] <> "", [Prev] <> "")), 1, 0)
ExitCheck - Checks to see if a record existed in previous month, but not the current month
ExitCheck = IF([Current Month] = "Exit", 1, 0)
I now want to summarize the ExitCheck flag. I have successfully done this with other measures, such as "TransferCheck" which is the following:
Transfer Count =
sumx(Values(Workday[Associate ID]),[TransferCheck])
This gives me my desired outputs, showing a count by Business for "Transfers". However, when I apply this exact same logic to "ExitCheck", i only get zeroes. Is there something different i should be doing?
Also, here is my (simple) data model
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
I created some data:
Here are the steps you can follow:
1. If you want to calculate the total of [ExitCheck], you can create measure:
all_sum = SUMX(ALL('Table'),'Table'[ExitCheck])
2. If you want to calculate [ExitCheck] in groups, you can create measure:
group_sum = SUMX(FILTER(ALL('Table'),[ExitCheck]=MAX('Table'[ExitCheck])),[ExitCheck])
3. Result:
If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous ,
I created some data:
Here are the steps you can follow:
1. If you want to calculate the total of [ExitCheck], you can create measure:
all_sum = SUMX(ALL('Table'),'Table'[ExitCheck])
2. If you want to calculate [ExitCheck] in groups, you can create measure:
group_sum = SUMX(FILTER(ALL('Table'),[ExitCheck]=MAX('Table'[ExitCheck])),[ExitCheck])
3. Result:
If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |