Forum Discussion

AndresEsteban's avatar
AndresEsteban
Icon for Helper I rankHelper I
4 years ago
Solved

Distinct count filtering by other column

Hello community I would need your help.

 

I have a table with the following records:
-Device: are different types of devices and sensors.
-Type of service: different types of services. Each device will belong to one of them
-Location: location where the service is deployed.

 

 

As can be seen in the attached example data, there can be more than one device of the same service in each location.

The problem I have is how to count the number of services I have in operation. For this, the logic is that even if there are different devices of the same service and therefore different rows with the same service for each location, that should only count as 1.
In the example, the expected result is as shown below:

 

Thank you in advance

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    AndresEsteban my bad please create a measure as below:-

    Measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Type of service],
        "sum", DISTINCTCOUNT ( 'Table'[Location] )
    ),
    [sum]
)

     

     

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi AndresEsteban ,

     

    You can directly add "Type of service" column and "location" column with summerized as distinct count on the table visual.

    output:-

     

    Refer the attached file.

    BR,

    Samarth

    • AndresEsteban's avatar
      AndresEsteban
      Icon for Helper I rankHelper I
      4 years ago

      Perfect, it works!!

       

      And another question, how could I calculate the total number of deployed services, in this case 7?

       

      Thank you