Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everone,
I have a report called worker data extract which is an appended long query, containing headcount-related demographic information and in a field called [Date] the date when the report was pulled which is the last day of each month. I have a related table ("Dim_Dates") in which all the days of the year are paired with a given label called [Month] in the order like the following:
01/07/2016 - 31/07/2016 as "F17 P01"
01/08/2016 - 31/08/2016 as "F17 P02"
...
01/10/2017 - 31/10/2017 as "F18 P04"
I added a column Month to my worker data extract which is =RELATED(Dim_Date[Month])
I have a bunch of dropdown filters in my report on my dashboard to alter filter context, [Month] included. If I select "F18 P04" as a Month on the filter I would like to see the Female represantation of the total headcount for 6 month before, as it was on "F17 P10".
Could you please tell me why this formula below is not working?
Female % 6 months prior =
VAR d =
MAX ( 'Fact_Worker Data Extract'[Date] )
RETURN
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
ALL ( 'Fact_Worker Data Extract'[Month] ),
'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ),
'Fact_Worker Data Extract'[Gender] = "Female"
),
CALCULATE (
DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ),
ALL ( 'Fact_Worker Data Extract' ),
'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 )
)
)
Hi @Anonymous,
Could you try the formula below to see if it works in your scenario?
Female % 6 months prior = VAR d = MAX ( 'Fact_Worker Data Extract'[Date] ) RETURN DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract' ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) && 'Fact_Worker Data Extract'[Gender] = "Female" ), CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract' ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) ) )
Regards
I would need to first somehow filter for the records which have a 6 months older month end date in their Date field and then do the divison. Is that possible?
Hi @Anonymous,
I would need to first somehow filter for the records which have a 6 months older month end date in their Date field and then do the divison. Is that possible?
In this scenario, you should be able to add other filters you need for calculation. The formula below is for your reference.
Female % 6 months prior = VAR d = MAX ( 'Fact_Worker Data Extract'[Date] ) VAR o = FIRSTNONBLANK ( 'Fact_Worker Data Extract'[organization], 1 ) RETURN DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract' ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) && 'Fact_Worker Data Extract'[Gender] = "Female" && 'Fact_Worker Data Extract'[organization] = o ), CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract' ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) && 'Fact_Worker Data Extract'[organization] = o ) )
Regards
Apologies, I used the wrong word, slicers, I mean.
So I have like 8 different slicers as well for users in order to select their own prefered combination, and I need the formula to work with those slicers as well. Is that possible?
Hi @Anonymous,
Could you try the formula below to see if it works?
Female % 6 months prior = VAR d = MAX ( 'Fact_Worker Data Extract'[Date] ) RETURN DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract'[Date] ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) && 'Fact_Worker Data Extract'[Gender] = "Female" ), CALCULATE ( DISTINCTCOUNT ( 'Fact_Worker Data Extract'[_Employee ID] ), ALL ( 'Fact_Worker Data Extract'[Date] ), 'Fact_Worker Data Extract'[Date] = EOMONTH ( d, -6 ) ) )
Regards
Hi,
Unfortunately that does not work, it returns blank.
Hi @Anonymous,
Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that I can further assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Hi again,
Thanks for the support and sorry for the late response - the issue still persists.
I created the pbix file to replicate the error. You may reach it on the following link:
https://drive.google.com/open?id=12QdDLQLMxHi9sGk4kMxRW0EK9fP7dM07
Many thanks in advance for your response!
Best regards,
György
Hi,
Thank you very much. That seems to work for one metric, but I would need to filter it further, broken down by different levels of the organization, etc. and with ALL() applied that can't happen. What if I need to show it as a table, broken down by another field's value in my dataset?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |