Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
buhari91
Frequent Visitor

DAX IF and SELECTEDVALUE to display multiple measure with slicer selection

Hi Power BI Community,

 

My requirement is to display
1. measure_new for Q2,
2. measure_old for any other quarters except 2022 - Q2
3. when select any quarter + Q2 - it should give the sum of selected quarters(measure_old) + 2022 Q2(measure_new) .

 

So, I have written this new measure:

test Q2 =
    IF(SELECTEDVALUE ( 'date_slicer'[Date].[Year] )="2022",
        IF(SELECTEDVALUE ( 'date_slicer'[Date].[Quarter])=="Q2",[measure_new],[measure_old]),
        [measure_old])

 

This works as expected when selection is everything except Q2, which looks like below:

buhari91_0-1665020238302.jpeg

 

or selection is only Q2:

buhari91_0-1665020831812.jpeg

 

 

Challenge:

When the selection includes everything including Q2, my code will just return measure_old which is not expected.
Expected output is (329+67=396)

buhari91_1-1665020901605.jpeg

 

I am struggling to write correct dax to store result for just the Q2 values in var1, store result of everything selected except Q2 in var 2 and finally sum those var1 and var2.

Another same challenge, in all my dax I am using measure to calculate measure which will slow down the performance ultimately.

Any ideas?? 

 

Thank you in advance 

3 REPLIES 3
buhari91
Frequent Visitor

@lukiz84 I tried to use VALUES function, but I was not able to store or use it.

This is something I am trying to achieve the task but failures again.

test Q2 =
var _min=DATE(2022,4,1)
var _max=DATE(2022,6,30)
var Q2Records =
IF(SELECTEDVALUE ( 'date_slicer'[Date].[Year] )=2022 && SELECTEDVALUE ( 'date_slicer'[Date].[Quarter] )=="Q2", COUNTROWS(Table1),0)
RETURN
IF(
    DATESBETWEEN(VALUES('date_slicer'[Date]),_min,_max),Q2Records,
        IF(ALLSELECTED ( 'date_slicer'[Date] ),CurrentQ2Referrals+COUNTROWS(Table2), COUNTROWS(Table2))
)


Thoughts??? Comments??

I don't really know what you are trying to achieve, but DATESBETWEEN is not what you think it is, ALLSELECTED neither.

 

 

lukiz84
Memorable Member
Memorable Member

SELECTEDVALUE only returns a value (not blank) when there is ONLY ONE (e.g. Q2) selected.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.