Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Another newbie to Power BI. I'm trying to calculate the difference in population between the min and max dates.
May data is set up like this:
Facility date pop_cnt
denver 3/1/2019 10,000
denver 4/1/2019 12,000
denver 5/1/2019 14,000
boulder 3/1/2019 3,0000
boulder 4/1/2019 5,0000
boulder 5/1/2019 7,0000
So, what I need to know is the difference in denver's population between march and may 2019 (14,000 - 10,000 = 4,000, then 4,000/10,000 = 4%) . Same thing for boulder. How do I do this in DAX ?
Thanks.
Solved! Go to Solution.
I'm not entirely sure what you want (a measure, a calculated column?) but this might do what you want or might be close enough so that you can tweak it:
[Your Measure] :=
var __isOneFacilityVisible = HASONEFILTER( FactTable[Facility] )
var __currentFacility = values( FactTable[Facility] )
var __minDate =
CALCULATE(
min( FactTable[date] ),
FactTable[Facility] = __currentFacility,
ALLSELECTED()
)
var __maxDate =
CALCULATE(
max( FactTable[date] ),
FactTable[Facility] = __currentFacility,
ALLSELECTED()
)
var __populationForMinDate =
CALCULATE(
VALUES( Table[pop_cnt] ),
FactTable[Facility] = __currentFacility,
FactTable[date] = __minDate,
ALLSELECTED()
)
var __populationForMaxDate =
CALCULATE(
VALUES( Table[pop_cnt] ),
FactTable[Facility] = __currentFacility,
FactTable[date] = __maxDate,
ALLSELECTED()
)
return
if(
__isOneFacilityVisible,
__populationForMaxDate - __populationForMinDate
)
I'm not entirely sure what you want (a measure, a calculated column?) but this might do what you want or might be close enough so that you can tweak it:
[Your Measure] :=
var __isOneFacilityVisible = HASONEFILTER( FactTable[Facility] )
var __currentFacility = values( FactTable[Facility] )
var __minDate =
CALCULATE(
min( FactTable[date] ),
FactTable[Facility] = __currentFacility,
ALLSELECTED()
)
var __maxDate =
CALCULATE(
max( FactTable[date] ),
FactTable[Facility] = __currentFacility,
ALLSELECTED()
)
var __populationForMinDate =
CALCULATE(
VALUES( Table[pop_cnt] ),
FactTable[Facility] = __currentFacility,
FactTable[date] = __minDate,
ALLSELECTED()
)
var __populationForMaxDate =
CALCULATE(
VALUES( Table[pop_cnt] ),
FactTable[Facility] = __currentFacility,
FactTable[date] = __maxDate,
ALLSELECTED()
)
return
if(
__isOneFacilityVisible,
__populationForMaxDate - __populationForMinDate
)
I'll have to try this, but I think it's what I need. Yes, I was trying to create a measure for this.
Thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |