Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DistinctCount with Filter of Measure

Hi,

 

I wanted to count the distinct serial number with

1) downtime lesser than 24 hours 

2) downtime lesser than 24 hours and lesser than 2 visits

 

I have 3 slicers for users to select.

 

The values I got for the 2 below measures are the same, what's the error?

 

<24 hours = 
CALCULATE(
    DISTINCTCOUNT('TableA'[Serial Number]),
     FILTER(ALLEXCEPT('TableA','TableB[Date],'TableA'[Priority],'TableC'[Entitlement]),
[Average Downtime (in hours)]<=24 
 ))

 

<24 hours & <2 visits = 
CALCULATE(
    DISTINCTCOUNT('TableA'[Serial Number]),
     FILTER(ALLEXCEPT('TableA','TableB[Date],'TableA'[Priority],'TableC'[Entitlement]),
 [Count of Service Visit]<=2 &&
 [Average Downtime (in hours)]<=24 
 ))
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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])

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , 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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

      By using your suggested solution, i still get the same number.

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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.