Filtering from MySQL database

[Anonymous]

Hi all,

I'm really new to PowerBI, but I'm creating a dashboard for the real estate company where I work to check our current apartment occupancy. All of our bookings go through our website, so all of our apartments have a 'status' column in the database. This column either says 0 (available), 1 (in option) or 2 (rented out). 

I'm trying to use the gauge visualization to check how many apartments have 'status' = 2 as the value (using COUNT in the visualization fields), with the maximum value being the COUNT of rows in the database. However, because I'm filtering the apartments with 'status' = 2, Power BI only counts the rows where 'status' = 2 for the maximum value. This results in being the value and the maximum value to be equal to eachother. 

Does anyone know how I can fix this? I've tried searching on the forums already, without any luck unfortunately...

[v-eachen-msft]

Hi @Anonymous ,

You could use two measures to show the result.

CurrentCount =
CALCULATE (
    COUNT ( 'Table'[apartmentno] ),
    FILTER ( 'Table', 'Table'[status] = 2 )
)

AllCount = 
COUNT('Table'[apartmentno])

When the database data is updated, you could click update button to refresh data. The measure will return the latest result.

[Anonymous]

Hi @v-eachen-msft, thanks for the help.

May I ask how I create two seperate measures? See the screenshot below on how I have my data filtered right now:

Count of apartmentid should be somewhere around 285, but because on the filter on status, it only counts the apartmentid's with status = 2.

I hope you'll be able to help 🙂

Edit: fiddled around with measures and figured it out using the measures @v-eachen-msft provided 🙂

[parry2k]

@Anonymous You question is clear but not sure what is your expected result. share sample data and expected output, it will help to get you the solution. 

[Anonymous]

@parry2k Will do! I will provide some small dummy data.

So I have my apartments table in my MySQL database:

apartmentno	status
1.0	2
1.1	1
1.2	2
1.3	2
1.4	0
1.5	0

In the example above, there are three 'rented out' apartments, two 'available' apartments and one 'in option' apartment, which makes for a total of six apartments.

In my gauge, I want the minimum value to be 0 (which is default), the maximum value to be 6 (the total apartments) and the (current?) value to be 3. I could manually set the maximum value to 6, but I'd like this value to be dynamic, since we are expected to rent out more apartments in the near future. When adding these apartments to our website, the maximum value would then automatically be updated out of the database.

I hope my expected result is more clear now 🙂