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.
Hello everyone,
I am new to power BI and I am trying to do the same we can do in Qlikview.
So, what I want is to show the count of employees, but:
- if no months are selected, I would like to filter by the max year-month that exists in the table. I am already doing this with a calculated column which formula is: IsLatestYearMonth = if('Calendar'[MonthID]=max('Calendar'[MonthID]), 1, 0 )
- if one month is selected, I would like to show the count of employees filtered for that month. I know one can do this by using:
test = IF (HASONEVALUE ('Calendar'[MonthID]),VALUES ('Calendar'[MonthID]),0)
Now, I am trying to integrate both formulas together into this one:
Headcount = calculate(DISTINCTCOUNT(Link[Employee Nr]), SOMETHING TO BE FILTERED BY DATE)
Solved! Go to Solution.
Hi @inescastelhano,
You can try to use below formula if it suitable for your requirement:
Result = IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),//check all selected or not select "Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee]),VALUES('Calendar'[Date])), "Last Month: "&MONTH(LASTDATE('Calendar'[Date])))
Notice: you should ensure these tables contains relationship and the cross filter direction is setting to 'both'.
Regards,
Xiaoxin Sheng
Hi @inescastelhano,
You can try to use below formula if it suitable for your requirement:
Result = IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),//check all selected or not select "Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee]),VALUES('Calendar'[Date])), "Last Month: "&MONTH(LASTDATE('Calendar'[Date])))
Notice: you should ensure these tables contains relationship and the cross filter direction is setting to 'both'.
Regards,
Xiaoxin Sheng
Thank you @v-shex-msft! I ended up with the formula:
IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),
"Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee Nr]),VALUES('Calendar'[Id_Date])),
"Last Month: "&calculate(DISTINCTCOUNT(Link[Employee Nr]), 'Calendar'[IsLatestYearMonth]=1))
Anyway your solution took me to the right place.
Thank you so much!
@inescastelhano sorry got distracted, did Xiaoxin solution work for you?
Proud to be a Super User!
you might have to use an if statement but maybe lastdate will work for you?
https://msdn.microsoft.com/en-us/library/ee634380.aspx
Proud to be a Super User!
Hi @vanessafvg, moreover I get an error saying "A function LASTDATE has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Thank you
Hi @vanessafvg, however LASTDATE receives and returns an argument of type DATE and there is no way for me to know the MonthID for that date, since I have several years. I can understand how to use the LASTDATE to get the last month with
Month(LASTDATE('Calendar'[Id_Date]))
, but not how to use it for filtering in CALCULATE expression.
Thank you,
Inês
Headcount = calculate(DISTINCTCOUNT(Link[Employee Nr]), SOMETHING TO BE FILTERED BY DATE)
Proud to be a Super User!
Should be something like this but it doesn't work:
Headcount = CALCULATE(
DISTINCTCOUNT ( Link[Employee Nr] ), 'Calendar'[IsLatestYearMonth]=if(MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date])),0,1),
FILTER ('Calendar',MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date]))),
FILTER('Calendar', YEAR('Calendar'[Id_Date]) = YEAR(LASTDATE ( 'Calendar'[Id_Date])))
)
but I get an error saying "The expression contains multiple columns, but only a single column can be used in a True/False epression that is used as a table filter expression".
you dont need to create 2 filters (it will behave like an or then)
Proud to be a Super User!
I get an error saying "The expression contains multiple columns, but only a single column can be used in a True/False epression that is used as a table filter expression"...
No you didn't misunderstand. I think you're getting to the point of my question.
However, I know this is not working properly because my headcount for the actual month is 20K and the result of this expression is 37K.
Moreover, I figured out this is responding to the selection, but it's not correctly calculating for the 'default' month, I also added the year to the expression to ensure it is calculating for the right year, but it's still not working.
test3 = CALCULATE(
DISTINCTCOUNT ( Link[Employee Nr] ),
FILTER ('Calendar',MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date]))),
FILTER('Calendar', YEAR('Calendar'[Id_Date]) = YEAR(LASTDATE ( 'Calendar'[Id_Date])))
)
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |