The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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])))
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
103 | |
103 | |
74 | |
59 |
User | Count |
---|---|
261 | |
123 | |
114 | |
97 | |
84 |