Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Counting Table rows in a relationship with another table, between dates!

Hi! I'm very much new to Power BI and I have issues all over haha, but I would love some guidance with this problem in particular!

I have three tables:

  1. A table with handheld devices (Used by users to create fines) - mobile_device
  2. A table with said fines given by said users -violation
  3. A table with the users - users

mobile_device and violation are both related by "hostname", a varchar, the unique name of the mobile_device

Each fine has a date/time value of the moment it was given.

I have created a table visualization by adding it mobile_device hostname, and violation id, and i made it show the total amount of violations per handheld device. I then added a splicer, putting in it the violation dates. This succesfully automatically filters the amount of violations given by each machine in the table, depending on the dates used on the filter!
Here's the issue, i would like to count the amount of machines that gave said fines in between the splicer dates and display it on a card, but when i place the hostname values on it, it only shows the total amount of machines without taking the fine dates into consideration. I wish i could count the amount of rows in the table visualization and be done with it, but it's in my understanding that you can't calculate based on visualizations.
I've tried using the Filter measure but PowerBI won't detect the relationships so the measure doesn't work...

Any guidance would be apreciated!
Tables

Tables!

Mobile Device Table
Mobile_device

Violations Table

Violation (a)

Violation(b)

Visualization

Visualization

General Comment
Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    so the violation-table seems to be your fact table.

    Why don't you count the IDs in the fact table. The following measure should give you the result:

    Distinct Hostnames = DISTINCTCOUNT( violation[hostname] )

     

    Like this you should get the distinct amount of different hostnames based on all filters.

    If you count the amount of different hostnames in the mobile_device table it always shows the total as there won't apply any other filters.

    Also think to create a proper data table, that will help you a lot when analyzing with time intelligence.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    According to your descriptions, it seems like a common date range analysis requirement.

    If that is the case, I'd like to suggest you take a look at the following blog 'start date', 'end date' part if it suitable to your requirement:

    Before You Post, Read This 

    Regards,

    Xiaoxin Sheng