Forum Discussion
DistinctCount with Filter of Measure
Anonymous , refer if this file can help
Formula I tried
<24 hours = CALCULATE(DISTINCTCOUNT('Table'[Serial Number]), FILTER('Table','Table'[Downtime (in hours)]<=24))
<24 hours & <2 visits = COUNTX(FILTER(SUMMARIZE('Table', 'Table'[Serial Number], "_1",COUNT('Table'[Visit])),[_1]<=2),[Serial Number])
Super UserAnonymous , as you are filtering measures you need to use values or to have the context of filter. Also need to allexpect need to be checked. Also allexpect will not be allcross table
Check this
<24 hours =
CALCULATE(
DISTINCTCOUNT('TableA'[Serial Number]),
FILTER(Table,
[Average Downtime (in hours)]<=24
),values ('TableA'[Serial Number]) , ALLEXCEPT('TableA','TableA'[Priority]) , ALLEXCEPT('TableB','TableB'[Date]), ALLEXCEPT('TableC','TableC'[Entitlement])
)
or
Countx(filter(summarize('TableA','TableA'[Priority],'TableB'[Date],'TableC'[Entitlement],'TableA'[Serial Number],"_1",[Average Downtime (in hours)]),[_1]<=24),[Serial Number])
Second one like
Countx(filter(summarize('TableA','TableA'[Priority],'TableB'[Date],'TableC'[Entitlement],'TableA'[Serial Number],"_1",[Average Downtime (in hours)], "_2", [Count of Service Visit]),[_1]<=24 && [_2] <=2),[Serial Number])
- amitchandak
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Because summarize way is best suited or values inside countx , that is not working, need to check with data.
Hi amitchandak ,
Here is the sample data.
For "Date" and "Entitlement", I have created another table to link with tables.
Hence, "Date" and Entitlement" slicer will be from TableB and TableC.
Below are the measures I used:
Average Downtime (in hours) = AVERAGE(TableA[Downtime (in hours)])Count of Service Visit = Count(TableA[Visit])<24 hours & <2 visits =
CALCULATE(
DISTINCTCOUNT(TableA[Serial Number]),
(FILTER(
GROUPBY('TableA',TableA[Serial Number],TableB[Date]),
[Count of Service Visit]<=2 &&
[Average Downtime (in hours)]<=24
)))Sample Data:
Serial Number Visit Date Downtime (in hours) Priority Entitlement A123 v-001 01-02-20 2 Emergency A A123 v-002 03-05-20 24 Low B A123 v-003 06-06-20 13 Non-Emergency A B321 v-004 12-03-20 12 Emergency B B321 v-005 30-01-20 64 Emergency A C123 v-006 06-02-20 23 Emergency A D123 v-007 05-02-20 14 Low A A123 v-008 15-12-19 12 Non-Emergency A D123 v-009 24-11-19 51 Emergency A B321 v-010 06-06-20 54 Emergency B C123 v-011 12-03-20 12 Emergency B A123 v-012 30-01-20 34 Emergency A E123 v-013 06-02-20 23 Emergency A F123 v-014 07-02-20 35 Emergency A G123 v-015 28-07-20 13 Emergency A H123 v-016 09-06-20 47 Emergency A I123 v-017 12-08-20 63 Emergency A I123 v-018 11-02-20 12 Emergency A E123 v-013 06-02-20 23 Emergency A E123 v-013 06-02-20 23 Emergency A Sample Output:
Date filter: year 20201. <24 hours = 5
2. <24 hours & <2 visits = 3