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

Don'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.

Reply
vjnvinod
Impactful Individual
Impactful Individual

Dax Help

hi Experts

 

i have two table calendar table connected to Forward Bookings table

 

vjnvinod_0-1713943104608.png

 

and i am using Calendar table period to slice and dice the values, like below

 

vjnvinod_1-1713943263748.png

In forward Bookings table i only have data from P10 onwards, so when i select P1, it shows me blank (which obviously what it is), but i am trying to achieve here is, when i selecte P1, if there is no data, it should still show me the available data like P9 to P12

 

this is my current Dax, please let me know, what i can modify in the dax to achieve the outputs as not blan

CALCULATE( SUM('Forward Bookings'[Hours]), 'Forward Bookings'[Engagement Type] = "External Engagement" )

 

 

 

3 REPLIES 3
v-jiewu-msft
Community Support
Community Support

Hi @vjnvinod ,

First of all, many thanks to @vanessafvg  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

vjiewumsft_0-1714026370904.png

2.Create the new measure to calculate hours.

 

Sum hours = 
VAR calendarPeriod = SELECTEDVALUE('Calendar'[Period])
var notbla = CALCULATE(
        SUM('Forward Bookings'[Hours]),
        FILTER('Forward Bookings','Forward Bookings'[Engagement Type] = "External Engagement" && 'Forward Bookings'[Period] = calendarPeriod)
    )
VAR blan = CALCULATE(
        SUM('Forward Bookings'[Hours]),
        'Forward Bookings'[Engagement Type] = "External Engagement",
        'Calendar'[Period] in {"Period9", "Period10", "Period11", "Period12"}
    )
RETURN
IF (
    calendarPeriod in {"Period9", "Period10", "Period11", "Period12"},
    notbla,
    blan
)

 

3.Select the slicer visual and drag the calendar period field into the visual.

vjiewumsft_1-1714026413153.png

4.Drag the measure into the table visual and select period1.

vjiewumsft_2-1714026423314.png

5.Select the Period10. The result is shown below.

vjiewumsft_3-1714026431681.png

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiewu-msft 

 

this needs to be dynamic, data can be anywhere, lets say P5, P9 etc, appears like the above dax, hardcodes the data

vanessafvg
Super User
Super User

why dont you just add a select all to your slicer settings?  i dont really understand the purpose of what you are asking,  you can achieve a similar result if you if you add a select all to your slicer in slicer settings (check the settings of the slicer to add this in) then you can just select all that and it will return only the period that have values?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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