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

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.

Reply
kaileena
Frequent Visitor

using slicer date value in dax formula

Hello,

 

I am unable to use the date selected from the slicer into my dax formula. The value returned is blank.

Note that my 'date' column is called "full formar for period". I created a continuous date column which i called "full format for period"

 

Value for Slicer Selected Month Budget Spent =
CALCULATE(
SUM('Measurement'[Value]),
'Measurement'[Type] IN { "Budget Spent" },
Measurement[Full Format for Period] = SELECTEDVALUE(ForSlicer[Full Format for Period].[Date])
)
 
kaileena_1-1658146523541.png

 

kaileena_2-1658146568840.png

 

kaileena_0-1658146697540.png

 

kaileena_0-1658146850504.png

 

kaileena_0-1658147525188.png

 


 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @kaileena 

in this case SELECTEDVALUE returns blank as it cannot find a single value of the date because you selecting a period (a month or multiple months) not a single date. Therefore as long as there is no blank dates in the Measurement Table, the filtered table will be empty. Therefore you need to use ALLSELECTED or VALUES to retrieve the selected dates, then you can use IN operator to check if current row date inside CALCULATE filter is within the selected period. Please try the following with either ALLSELECTED or VALUES

Value for Slicer Selected Month Budget Spent =
CALCULATE (
    SUM ( 'Measurement'[Value] ),
    'Measurement'[Type] = "Budget Spent",
    Measurement[Full Format for Period]
        IN VALUES ( ForSlicer[Full Format for Period] )
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @kaileena 

in this case SELECTEDVALUE returns blank as it cannot find a single value of the date because you selecting a period (a month or multiple months) not a single date. Therefore as long as there is no blank dates in the Measurement Table, the filtered table will be empty. Therefore you need to use ALLSELECTED or VALUES to retrieve the selected dates, then you can use IN operator to check if current row date inside CALCULATE filter is within the selected period. Please try the following with either ALLSELECTED or VALUES

Value for Slicer Selected Month Budget Spent =
CALCULATE (
    SUM ( 'Measurement'[Value] ),
    'Measurement'[Type] = "Budget Spent",
    Measurement[Full Format for Period]
        IN VALUES ( ForSlicer[Full Format for Period] )
)
technolog
Super User
Super User

Hi! Try to remove some parts from formula:
Value for Slicer Selected Month Budget Spent =
CALCULATE(
SUM('Measurement'[Value]),
'Measurement'[Type] IN { "Budget Spent" }
)
 

i did. i think the problem comes from how my tables are connected. (Or not connected. )

 

This is the current outcome.it does not show the correct value. i will explain.

kaileena_0-1658148489220.png

 

this is the model. The value i need to aggregate is in the table "measurement" which has a column called "full format for period". For a different use case, i created the "ForSlicer" table (

ForSlicer = VALUES('Measurement'[Full Format for Period]) ). Of course the "ForSlicer" table is used in the Slicer.
 
I did not connect "ForSlicer" with the "measurement" table because it does not suit my previous use case. Previous use case needs to have the two tables not connected. Previous use case show the last 12 months wrt to slicer selection. So i guess the value 2.61 is the sum of budget for the 12 months selected.

kaileena_1-1658148909203.png

kaileena_2-1658149005307.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors