New Card Visual - Editing interactions for a single card

[Anonymous00729]

Hi all,

I have another question. I’m using a new card visual where I want to display the Best Performing Store. If there was a change in the best performing store, I want to show that in my reference label.

However, I have two slices: a Month slicer and a Store Location slicer. When I select Jan from the Month slicer, my card shows me Astoria as the best performing month. This isn’t true because, there were no prior sales in Dec. Hell’s Kitchen was the best performing store throughout all months (From Jan to Jun). How do I get my card to show a blank for Jan, like the rest of my cards shown below and how do I disable my Store Location slicer for that Best Performing Store Card?

Here are the DAX measures that I have so far:

Best Performing Store =

CALCULATE (

    FIRSTNONBLANK (

        TOPN (

            1,

            VALUES ( 'Store'[store_location] ),

            [Total Revenue], DESC

        ),

        1

    )

)

Previous Month Best Store =

VAR PrevStore =

    CALCULATE (

        FIRSTNONBLANK (

            TOPN (

                1,

                VALUES ( 'Store'[store_location] ),

                [Total Revenue], DESC

            ),

            1

        ),

        DATEADD ( 'Date'[Date], -1, MONTH ),

        REMOVEFILTERS ( 'Store'[store_location] )

    )

RETURN

IF ( ISBLANK(PrevStore), BLANK(), PrevStore )

Best Performing Store ▲ =

VAR CurrentStore  = [Best Performing Store]

VAR PreviousStore = [Previous Month Best Store]

RETURN

IF (

    CurrentStore = PreviousStore,

    "No change",

    "Changed from " & PreviousStore & " to " & CurrentStore

)

Please can someone assist me, thank you so much in advance.

[Ahmed-Elfeel · Accepted solution]

Hi @Anonymous00729,

I hope you are doing well today ☺️❤️

So you have 3 Issues need to be Solved:

• Blank Previous Month in January
• Store Location Slicer Affecting Best Store Calculation
• Changing Indicator Showing Incorrect Message

First Issue

The Issue is that your Previous Month Best Store measure doesnt properly handle the case when there is no data for the previous month. Here is the corrected measure:

Previous Month Best Store =
VAR CurrentMonthStart = MIN('Date'[Date])  // First day of selected month
VAR PreviousMonthStart = DATE(YEAR(CurrentMonthStart), MONTH(CurrentMonthStart) - 1, 1)
VAR PreviousMonthEnd = EOMONTH(CurrentMonthStart, -1)

VAR HasPreviousMonthData = 
    NOT ISBLANK(
        CALCULATE(
            [Total Revenue],
            'Date'[Date] >= PreviousMonthStart,
            'Date'[Date] <= PreviousMonthEnd,
            REMOVEFILTERS('Store'[store_location])
        )
    )

VAR PrevStore =
    IF(
        HasPreviousMonthData,
        CALCULATE(
            FIRSTNONBLANK(
                TOPN(
                    1,
                    VALUES('Store'[store_location]),
                    [Total Revenue], DESC
                ),
                1
            ),
            'Date'[Date] >= PreviousMonthStart,
            'Date'[Date] <= PreviousMonthEnd,
            REMOVEFILTERS('Store'[store_location])
        ),
        BLANK()
    )
RETURN PrevStore

Second Issue

You need to modify your Best Performing Store measure to ignore the Store Location slicer:

Best Performing Store =
CALCULATE(
    FIRSTNONBLANK(
        TOPN(
            1,
            VALUES('Store'[store_location]),
            [Total Revenue], DESC
        ),
        1
    ),
    REMOVEFILTERS('Store'[store_location])
)

Final Issue

• You need to Update Change Indicator Measure too Look like that:

Best Performing Store ▲ =
VAR CurrentStore = [Best Performing Store]
VAR PreviousStore = [Previous Month Best Store]
RETURN
SWITCH(
    TRUE(),
    ISBLANK(CurrentStore) && ISBLANK(PreviousStore), BLANK(),
    ISBLANK(CurrentStore) || ISBLANK(PreviousStore), BLANK(),
    CurrentStore = PreviousStore, "No change",
    "Changed from " & PreviousStore & " to " & CurrentStore
)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Anonymous00729]

Hi @Ahmed-Elfeel,

I'm good thanks and hope you are too 😊 ❤️

Thank you so much for taking the time out to assist me over these last two days. This solution was perfect! I could only wish to be as talented as you guys are here in this forum. I'm still finding my way around DAX as I'm transtioning into the field, so I do run into some trouble when working on these projects.

Thank you once again, I greatly appreciate it.

[Ahmed-Elfeel]

Hi @Anonymous00729,

I am really glad to hear the solution worked for you! 😊
And thank you for the kind words that truly means a lot ❤️

You are doing great already! Transitioning into DAX and Power BI takes time and asking questions is the best way to grow. We all started somewhere and with the progress you are making, you will be helping others here in no time!

Always happy to help feel free to ask whenever you get stuck again 👍

[Anonymous00729]

Hi @Ahmed-Elfeel

Thank you so much for your kind words and encouragement. I really appreciate it! 😊

This is one of the most helpful communities I've been on and it's a priviledge to learn from you and others in this forum ❤️.

I really do hope to give back to others some day when I do become more well-versed in Power BI 😅.

Until next time... Have a great weekend! 😊

[FBergamaschi]

If I understand correctly the problem is that this measure

Previous Month Best Store =

VAR PrevStore =

    CALCULATE (

        FIRSTNONBLANK (

            TOPN (

                1,

                VALUES ( 'Store'[store_location] ),

                [Total Revenue], DESC

            ),

            1

        ),

        DATEADD ( 'Date'[Date], -1, MONTH ),

        REMOVEFILTERS ( 'Store'[store_location] )

    )

RETURN

IF ( ISBLANK(PrevStore), BLANK(), PrevStore )

returns to you a store with no sales (Astoria)

If that is correct,

Previous Month Best Store =

VAR PrevStore =

    CALCULATE (

        FIRSTNONBLANK (
          FILTER(
            ADDCOLUMNS (

            TOPN (

                1,

                VALUES ( 'Store'[store_location] ),

                [Total Revenue], DESC

            ),
            "@SalesVal", [Total Revenue]
          ),
          [@SalesVal] > 0
          ),

            1

        ),

        DATEADD ( 'Date'[Date], -1, MONTH ),

        REMOVEFILTERS ( 'Store'[store_location] )

    )

RETURN

IF ( ISBLANK(PrevStore), BLANK(), PrevStore )

Hope this helps getting a blank value for the prior month best store

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Anonymous00729]

Hi @FBergamaschi, thank you so much for taking the time out to assist me. I greatly appreciate it! Unfortunately this solution didn't work for me. But, the one @Ahmed-Elfeel suggested worked perfectly.