Dax formula to display all the dates in the data set on the header (Card visualization)

[Anonymous]

Hello,

Need help with a DAX formula modification or a new DAX formula. User wants to see all the weekending dates that is available in the data set on a card visualization header on top of the report. when the report opens it is missing with the latest weekending date on the header even the data set has the latest weekending date. User has two options in the filter pane (circled in red color in the screen shot)

1. All  (issues choosing all option ,missing  the latest weekending date)

2. Is Latest (no issues, displays only the latest weekending data)

Please check the screen shots (Weekend Date) that displays weekending dates on the report and also check the screen shot that has the DAX formula that is used to display the dates.

[danextian · Accepted solution]

Hello,

You can modify your formula a bit so that  when ALL is selected from the slicer/filter pane, all dates are displayed. You can do something like this:

Measure =
VAR __All =
    //use ALL function so all rows are included
    CALCULATE (
        CONCATENATEX ( VALUES ( Table[Date] ), Table[Date], "," ),
        ALL ( Table )
    )
VAR _Latest = <your expression/measure>
VAR __Selected =
    SELECTEDVALUE ( Table[IsLatest] )
RETURN
    //returns all when none is selected
    SWITCH ( __Selected, "All", __All, "Latest", _Latest, __All )

[Anonymous]

It Works,Thank you..

[kentyler]

You are doing an ALL over weekends in the second IF, but not in the first

It might need an ALL to counteract the results of the filter the user can put on the page.

I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.