Counting items in a range of dates filtered by a visual

[Syndicate_Admin]

Good day, I come to your aid.

I'm pretty new to Power Bi and DAX and I'm stuck with a few things. I have a table that shows me at what time of day a device is turned on (Activity), and I have another table that contains only the names of all the devices that can be turned on (Devices), what I want is to be able to generate a table, column or a visual element per se that shows me which devices were not turned on during a date range filtered by visual elements. I attach models of the tables and also a capture of the image of the visual elements.

Activity Table:

Devices Table

Visuals

If you could guide me, or suggest a past post that deals with a similar topic I will be very grateful.

✌️

[Anonymous]

Hi @ampeers ,

You can try to create a measure as below to get it:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[device_id] ),
    FILTER (
        'Table',
        'Table'[status_caption] = "Radio Encendido"
            && 'Table'[date] >= MIN ( 'Date'[Date] )
            && 'Table'[date] <= MAX ( 'Date'[Date] )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Syndicate_Admin]

Hello, sorry for the delay in uploading and sharing the information.

Below I share a link with my files, there is an excel file that has the tables and what would be expected, this topic only has one observation.

https://1drv.ms/u/s!Ag-9V9cclE2iklx7YeUBBfiJJWQP?e=hf1Odg

Thank you for your help😃

[vanessafvg]

are you able to share data in text format or a power bi file?

[Syndicate_Admin]

I share a link with the files in question.

https://1drv.ms/u/s!Ag-9V9cclE2iklx7YeUBBfiJJWQP?e=hf1Odg

Thanks a lot!

[Anonymous]

Hi @ampeers ,

After reviewing your example file, I found that it is not necessary to create the calculation tables Actividad and Dispositivos, you can create the following two measures and follow my updated pbix file (see page "Duplicate of Página 1" of the attachment for details ) to create visuals to achieve your needs.

Number of turn on devices =
CALCULATE (
    DISTINCTCOUNT ( 'NotifyChangeStatusMessages'[device_name] ),
    'NotifyChangeStatusMessages'[status_caption] = "Radio Encendido"
)

Number of turn off devices =
CALCULATE (
    DISTINCTCOUNT ( 'NotifyChangeStatusMessages'[device_name] ),
    'NotifyChangeStatusMessages'[status_caption] = "Radio Apagado"
)

PS: The data of the visuals in the screenshot below cannot be loaded properly because there is no proper authorization information... Anyway, it will display normally if you open the file in your side....

Best Regards

[Syndicate_Admin]

Hello @Syndicate_Admin

Thank you for the input, but it's not what I want to achieve.

The devices go from a state "turn_on" when they are used and then to "turn_off" automatically when there is no activity, this happens several times in the day, what I want to achieve is to know which of all these devices are not used in the day, week, month or year, through the filters of the visual elements.

My logic was to be able to count how many times the device is activated in the day, week, month or year and those that have a value of 0 times would be my elements not activated in the day.