Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I want to find a way to create 1 measure that assosiates to 2 different timestamps.
My data has 3 columns:
1) Sales Date (Year)
2) Departure Date (Year)
3) Sales
i.e.
Sales Date | Depature Date | Sales
2018 | 2018 | $1000
2018 | 2019 | $500
2019 | 2019 | $2000
2019 | 2020 | $3000
I wanted to create a static 2019 sales measure - depends on my column selected in my visual:
1) Sales Date: Sales (2019) = $5000
2) Departure Date: Sales (2019) = $2500
So somehow I need to embed this logic in the Calulate(SUM(sales)) filter:
Sales Date = 2019, Departure Date = Any
Sales Date = Any, Depature Date = 2019.
Appreciate if anyone can share some idea.
Thanks.
Solved! Go to Solution.
Hi @Bobster
Please refer to attached sample file wit the solution
Sales Amount =
SWITCH (
TRUE ( ),
ISFILTERED ( 'Table'[Sales Date ] ),
CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[Sales Date ] = 2019, ALL ( 'Table' ) ),
ISFILTERED ( 'Table'[ Depature Date ] ),
CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[ Depature Date ] = 2019 ),
SUM ( 'Table'[ Sales] )
)
Hi @tamerj1 , appreciate your help. I've slightly modified the DAX, i.e. removed the "ALL (Table)" so the measure gives me the breakdown when other columns were added i.e. Month, and it worked.
Only out of curiosity, the ISFILTERED detecte 1 table/field at a time. What if user want to display and filter sales Sold and Departed in 2019 ($2000)?
Thanks
Hi @Bobster
Please refer to attached sample file wit the solution
Sales Amount =
SWITCH (
TRUE ( ),
ISFILTERED ( 'Table'[Sales Date ] ),
CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[Sales Date ] = 2019, ALL ( 'Table' ) ),
ISFILTERED ( 'Table'[ Depature Date ] ),
CALCULATE ( SUM ( 'Table'[ Sales] ), 'Table'[ Depature Date ] = 2019 ),
SUM ( 'Table'[ Sales] )
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
13 |
User | Count |
---|---|
125 | |
37 | |
31 | |
27 | |
24 |