Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I just started to work with Power Bi and I couldn't find a way to solve my problem.
Here an exemple of my table :
My goal is to get the Max-Min of 'Consommation chauffage (Wh)' for each day and each apartment.
There is apartment I1 but also G1 so I would like a filter on 'Etage', 'Numero appartement' and 'Date'.
Solved! Go to Solution.
OK, Thank you @az38 for your help. I finally manage to do it.
I didn't use your way for the date but I simply use 'Table'[Date].[Year]; 'Table'[Date].[Month]; 'Table'[Date].[Day] in ALLEXCEPT
So I have :
Test = CALCULATE(MAX(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour])) -
CALCULATE(MIN(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour]))
Hi @Anonymous
try a measure
Measure =
var _date = SELECTEDVALUE('Table'[Date])
var _day = DATE(YEAR(_date), MONTH(_date), DAY(_date))
RETURN
CALCULATE(MAX(Table[Consommation chauffage (Wh)]), ALLEXCEPT(Table, Table[Etage], Table[Numero appartement]), DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),DAY('Table'[Date]))=_day) -
CALCULATE(MIN(Table[Consommation chauffage (Wh)]), ALLEXCEPT(Table, Table[Etage], Table[Numero appartement]), DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),DAY('Table'[Date]))=_day)
it can be much easier but Im not sure about full your data model
Hi @az38
The measure you give me is empty when I try it.
I'm not sure to understand how ALLEXCEPT work exactly.
It says that it return all rows of a table except those affect with the columns specified. But I'm not sure if it does what I really want
Maybe to clarify my purpose here what I want :
The MAX (and MIN) of all 'Consommation chauffage (Wh)' where, for example, : 'Etage' = I AND 'Numero Appartement' = 1 AND 'Date' = 19/08/2019 xx:xx:xx
A IF condition won't be a solution because of the amount of possibilities, of course.
@Anonymous
for me it works pretty good
alexcept() says to powerBI: hey, take all the 'Table' and calculate MIN(Consommation chauffage (Wh)) value for all rows with this Etage and this Numero Appartement
I cannot use the same for the date, because you are using DateTime format and it is different for each rows, so i have to add another special filter for DAY only
OK, Thank you @az38 for your help. I finally manage to do it.
I didn't use your way for the date but I simply use 'Table'[Date].[Year]; 'Table'[Date].[Month]; 'Table'[Date].[Day] in ALLEXCEPT
So I have :
Test = CALCULATE(MAX(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour])) -
CALCULATE(MIN(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour]))
@Anonymous
I do not like to use these Date part appends because it not always work as i expect. anyway, if its work - thats great!
Ok, I understand ALLEXCEPT
But I don't understand why that doesn't work with me..
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |