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,
I have a table with a school and its date of visit.
I would like, on a report with a filter per year, to display an indicator with the number of schools that have not been visited before.
I imagine that we need a measure to make this indicator, but I do not see how to formulate it.
Example of datas :
School1 ; 17/01/2017
School1 ; 18/05/2018
School2 ; 25/11/2018
School3 ; 15/07/2018
Desired result :
If I filter on 2017 : 1 (there is only school1 visited on 2017)
If I filter on 2018 : 2 (there is 3 schools visited on 2018, but school1 was already visited on 2017 so I don't want count it)
Any idea ?
Thanks in advance,
Florent
Solved! Go to Solution.
Hi @florentbignier,
Please refer to below measures:
check = CALCULATE ( COUNT ( Table1[School] ), FILTER ( ALLEXCEPT ( Table1, Table1[School] ), Table1[Date].[Year] = YEAR ( SELECTEDVALUE ( Table1[Date] ) ) - 1 ) ) School Visits by year = CALCULATE ( DISTINCTCOUNT ( Table1[School] ), FILTER ( ALLSELECTED ( Table1 ), [check] <> 1 ) )
Best regards,
Yuliana Gu
Hi @florentbignier,
Please refer to below measures:
check = CALCULATE ( COUNT ( Table1[School] ), FILTER ( ALLEXCEPT ( Table1, Table1[School] ), Table1[Date].[Year] = YEAR ( SELECTEDVALUE ( Table1[Date] ) ) - 1 ) ) School Visits by year = CALCULATE ( DISTINCTCOUNT ( Table1[School] ), FILTER ( ALLSELECTED ( Table1 ), [check] <> 1 ) )
Best regards,
Yuliana Gu
All you need is COUNT formula. See sample file
Steps:
1) Add measure "School Visits by Year" to table, to count number of visits
2) Add measure to page as table
3) Drag "Date" to screen and change to Slicer
4) Click on Slicer and then from the "Field" part of the Slicer, right click on "Date" and choose "Date Hierarchy" so you get the year
5) Change Slicer to List format
Thanks chirayuw 🙂
Unfortunately your sample file gives me "3" on "2018", but I don't want to count school1 because this school has already been visited on 2017.
Any other idea ?
Florent
try this. current year minus previous year. distinct count of school name by year
School Visits by year = VAR CYear //Current Year selected in Slicer =SELECTEDVALUE(Table1[Date].[Year]) VAR PYear //Previous Year = Above minus 1 =SELECTEDVALUE(Table1[Date].[Year])-1 VAR CCOUNT // Do a distinct count of schools based on school name visited in current year =CALCULATE(DISTINCTCOUNT(Table1[School]),FILTER(ALL(Table1),Table1[Date].[Year]=CYear)) VAR PCOUNT // Do a distinct count of schools based on school name visited in previous year =CALCULATE(DISTINCTCOUNT(Table1[School]),FILTER(ALL(Table1),Table1[Date].[Year]=PYear)) Return //current year count minus previous year count CCOUNT-PCOUNT
Interesting, but...
If a school isn't visited in 2018, your formula will give me false result
I complete my data sample :
School4 ; 01/07/2017
School1 ; 17/01/2017
School1 ; 18/05/2018
School2 ; 25/11/2018
School3 ; 15/07/2018
In this case I want :
If I filter on 2017 : 2 (school1 and school4)
If I filter on 2018 : 2 (school2 and school3 but not school1 which was already visited on 2017)
Many thanks for youy time !
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |