Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I don't have much experience using IF for creating a measure and just stumbled across switch today, but it appears that if I want to create a measure using either, I can only use measures that I've already created rather than a a column in my table?
In my specific case I have a table that has 2 different addresses for the beginning and end of a year; some of the addresses change at certain dates and some remain the same. I wanted to create a dynamic measure for the current address based on the date using IF - i.e. if the date of change is greater than today or blank then the 'current' address is the one listed in the beginning of year column, and if the date of change is less than today then the 'current' address is the one listed in the end of year column. However, I'm not able to select either of the columns when attempting to create a measure. So is my best option here to create a new column that will indicate the current address based on dates?
Solved! Go to Solution.
Hi @mterry,
Please try a measure like this:
Measure = IF ( MIN ( 'Table1'[Move Date] ) > TODAY () || ISBLANK ( MIN ( 'Table1'[Move Date] ) ), MIN ( Table1[Address] ), MIN ( Table1[New Address] ) )
Best Regards,
Dale
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYep, that's what smoupre said.
smoupre: here's a sample of the data - the expected Current Locaiton for No. 1 would be (using TODAY() and assuming it's 2/3/2018), would be Acorn Street, for No. 2 it would be Bling Street, for No. 3 it would be High Street, and No. 4 it would be Mountain Street. Is the best option here to create a new column for the current location using the dates?
No. | Address | New Address | Move Date |
1 | Acorn Street | Camp Street | 10/31/2018 |
2 | Doe Street | Bling Street | 2/1/2018 |
3 | High Street | 1st Street | 8/1/2018 |
4 | Car Street | Mountain Street | 1/1/2018 |
Hi @mterry,
Please try a measure like this:
Measure = IF ( MIN ( 'Table1'[Move Date] ) > TODAY () || ISBLANK ( MIN ( 'Table1'[Move Date] ) ), MIN ( Table1[Address] ), MIN ( Table1[New Address] ) )
Best Regards,
Dale
It looks like that worked, thank you so much! I don't (or didn't until now) have any experience with the || (or &&) operators, those look like they will be very helpful going forward, I appreciate it.
If you reference a column in a measure, you must use the fully qualified name (Table[Column]) and you must use an aggregator like MAX, MIN, SUM, etc.
@Greg_Deckler
Which aggregator would you use in a SWITCH TRUE() -measure where you need to find if (Calendar[Date]) is weekday or saturday/sunday?
"value1": (Calendar[Date]) = "Weekday";
"result1",
"value2": (Calendar[Date]) = "Saturday";
"result2"
"value3": (Calendar[Date]) = "Sunday";
"result 3"
I can't fit an aggregator like MIN, MAX, SUM.. It seems to me that Calculate in combination with Filter might be a solution, but I'm currently not qualified enough to achieve that.
The required result is:
IF (Calendar[Date] = "Weekday" then SalarisNormaal + SalarisOVW1 + SalarisOVW2
IF (Calendar[Date] = "Saturday" then SalarisZaterdag + SalarisZatOVW1 + SalarisZatOVW2
IF (Calendar[Date] = "Sunday" then SalarisZondag + SalarisZonOVW1 + SalarisZonOVW2
Thanks in advance.
Salaris1 =
VAR SalarisNormaal =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenNormaal]
* RELATED ( Personeel[Uurloon] )
)
VAR SalarisOVW1 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW1]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw1] )
)
VAR SalarisOVW2 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW2]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw2] )
)
VAR SalarisZaterdag =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenNormaal]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_zaterdag] )
)
VAR SalarisZatOVW1 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW1]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw1] )
)
VAR SalarisZatOVW2 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW2]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw2] )
)
VAR SalarisZondag =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenNormaal]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_zondag] )
)
VAR SalarisZonOVW1 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW1]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw1] )
)
VAR SalarisZonOVW2 =
SUMX (
'Eigen overzichten',
'Eigen overzichten'[UrenOVW2]
* RELATED ( Personeel[Uurloon] )
* RELATED ( Opdrachtgever[Loon_ovw2] )
)
RETURN
SWITCH(
TRUE(),
@krampit1 Please don't highjack a solved post from over 3 years ago. Just post as a new question in the forums. Please.
Okay got it, so filtering through individual rows based on dates won't be an option it seems. Thanks
Depends, you can use CALCULATE with FILTER clauses to filter down to individual rows. I'd really need to see some sample/example data and expected output to steer you in the right direction.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
141 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |