Different metrics in the same graph but filtered by different dimensions from the same table

[kidsofr]

Hi !

I'm quite new using Power BI and I cannot find any related solution in the forum for my problem.

I hope my explanation is clear, otherwise feel free to ask more details ! 😊

Context : 

I have two tables : Houses and Bookings linked as : (Houses) 1 => many (Bookings) on idlocation

 - Houses Table's main fields : idlocation, reference (unique business id for each house, there is one reference for one idlocation which is more of a technical id), status (online/offiline), capacity, quality, with or without wellness, and lots of other dimensions.

- Bookings Table's main fields : Idbooking, booking date, booking origin..

See exemple data at the end of the post

Objective

I want to represent in a bar chart the average number of bookings per house according to the booking date for 3 different clusters of houses (2 out of 3 of these clusters can be dynamically "personnalised" by the user of the report by using the segments present in the dashboard).

I have then to build 3 different metrics that should be filtered differently according to the cluster: 

  1. One should be filtered only by a segment corresponding to the reference (unique selection => Just the number of bookings of that selected house) but none of the other filters (cF. second cluster). The reference segment is visible because the user can choose the house he wants to have a look at.

  2. One should be a cluster made from houses that can be personnalised by the user by using the different segments and slicers that corresponds to the dimensions of the house table : capacity, quality, wellness... that are visible on the report. There are a lot of these potential segments, while the first cluster has to be filtered only by the reference one.

 3. One should not consider any filters : It is the total number of bookings divided by the total number of houses for online houses. (the whole house portfolio we have)

In addition, the 3 of them are filtered on the status with value "Online", but that specific is on the filter panel on the right of the screen as the user cannot change it, not in the dax formula.

In addition, the 3 of them are filtrered by the "booking origin" inside the metric's formula as I need to duplicate these metrics according to the origin of the booking to display them also in the graphs.

Problem

My problem is that I haven't managed to find a DAX Formula for the 1st and 2nd cluster. If I select for e.g. in the reference segment the house n°1052 that has a capacity of 10, while I select in the capacity segment the value "15" because I want to compare them, no data shows off for these 1st and 2nd metric in the bar chart.

I have tried lots of different combination of calculate( all/filter/allexcept/removefilters & values,..) but nothing worked yet.

E.G for the first metric : 

# Bookings AE clstr 1 = CALCULATE( COUNT(‘bookings'[idbooking] ), ALLEXCEPT(‘houses',’houses'[reference],’houses'[status]), FILTER(All(‘bookings'[bookingorigine]), ‘bookings'[bookingorigine]="AE")) / CALCULATE(DISTINCTCOUNT(‘houses'[idlocation]), ALLEXCEPT(‘houses',’houses'[reference],’houses'[status]))

I would like to avoid to duplicate the house tables or to use different graphs with different interactions with the segments.

I would really appreciate your help ! 😊

Thanks and Kr,

Exemple dummy data (all figures are in number format, date in date, rest in text): 

Bookings Table

idbooking -	booking date -	booking origin -	idlocation
1	05-01-22	AE	5
2	05-06-22	PP	1
3	04-03-22	AE	1
4	15-07-22	PP	4
5	06-08-22	PP	3
6	16-04-22	AE	2
7	01-06-22	AE	1

House Table

idlocation -	reference -	Capacity -	Quality -	Status -	Wellness
1	1052	10	3	Online	1
2	3257	3	4,5	Offline	0
3	2485	15	5	Online	0
4	2458	2	3,1	Online	0
5	2478	5	1,9	Online	1

[kidsofr]

Hi Felix, 

thanks for your quick answer.

Indeed, I understand better the problem now, but I haven't been able to find a work around. I will try to adapt my formulas with the exemple you wrote.

Concerning your question about my clusters : 
1st Cluster : I want only the bookings (count of idbookings) related to the house selected by the reference segment (column of the house table). I should then not consider any of the others slicers/segments that use as fields the other columns of the house table, including capacity, quality, wellness (I have lots of other columns in this table I have to filter with).
2nd Cluster : I want only the bookings related to the sample of houses as filtered by the columns of the house table, , including capacity, quality, wellness etc. And therefore it does not have to consider the reference segment.

3rd Cluster : I need the bookings of all houses, whatever the filters in the report on the reference, capacity, quality, wellness etc.
Additionnaly, I use the status column as a filter for all the page on this report with the value "online"

I need also to have for all these 3 clusters a version filtered on each value (either "Client", either "Owner") of the bookingorigin column (bookings table). So in total 6 metrics, 2 by clusters.

Hope it is more understandable that way ? 

Best Regards,

[MFelix]

Hi @kidsofr ,

Believe your issue is because you are making the slicing by the same columns from the tables you have that will cause your data to get incorrect, to better understanding let me pick up your example:

When you do a filter on your table by the capacity 15 you get the value of line 3

idlocation -	reference -	Capacity -	Quality -	Status -	Wellness
3	2485	15	5	Online	0

However when you then apply the reference of the house 1052 this is applied to the previous table so you do not have any data with that information and your table is getting blank so no result.

To make this type of calcuation you need to have disconnected tables to use on the slicers / filters that you then can use on your metrics that would look similar to this:

Count of house 15 =
var LocationSelection = 
SELECTCOLUMNS(
FILTER( House, House[idlocation] in VALUES(DisconnectedTable[idLocation]), "IDLocationVAlue", House[idlocation])
Return
 CALCULATE ( COUNTROWS(Bookings), BOOKINGS[Idlocation] in LocationSelection )

This is just an approach without any sample since I have a question about your Cluster, that is what columns are you using to filter out the clusters?

Sorry for the additional questions but just want to give you a robust answer.