Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Syndicate_Admin
Administrator
Administrator

Counting items in a range of dates filtered by a visual

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:

ampeers_0-1654293067633.png

Devices Table

ampeers_1-1654293164072.png

Visuals

ampeers_3-1654293226358.png

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

✌️

6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I share a link with the files in question.

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

Thanks a lot!

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....

yingyinr_0-1654674535812.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.