Stumped! Get Row Header Value

[littlemojopuppy]

Hi!  Would like to ask for some help with something.  I'm trying to implement logic to handle data with different granularities, with one exception...the result isn't a measure.  It's hard coded based on year/month.  The reason I'm doing this instead of using a table (which would be so much easier!) is because the data source is an analysis services Live Connection, so I can't create another table.

I wrote two measures...here's the first:

Targets = 
    IF(
        ISFILTERED('Calendar'[Date]),
        BLANK(),
        SWITCH(
            TRUE(),
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 4
            ), 796,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 5
            ), 1296,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 6
            ), 1796,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 7
            ), 2296,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 8
            ), 2796,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 9
            ), 3296,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 10
            ), 3796,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 11
            ), 4396,
            AND(
                SELECTEDVALUE('Calendar'[Year]) = 2020,
                SELECTEDVALUE('Calendar'[MonthNumber]) = 12
            ), 4996,
            BLANK()
        )
    )

The second measure is just the SWITCH statement from the above code.

Here's the results from each measure

The second measure is correct, in that it's applying the right value at the month level but unfortunately it's also at the day level which is misleading.  The first measure (the one with the ISFILTERED statement) is correct in that nothing is applied at the day level, but unfortunately there's nothing at the month level either.  I think the reason is because DAX can't determine a single year and month (see my testing stuff in the multi-row card).  But for the life of me, I haven't been able to figure out how to reference the year/month in the row header when it works as intended in the other measure.  😫

Could anyone offer a hint or two on how to correctly get the values for the row and year in the row headers?  Would appreciate any help that could be given!

[Anonymous]

HI @littlemojopuppy,

I'd like to suggest you take a look at the following blog about hierarchy level handle on DAX formula:

Clever Hierarchy Handling in DAX 

If above not help, please share some dummy data with a similar data structure to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[littlemojopuppy · Accepted solution]

Good morning!

I took a look at the article...did not help.  Because it's a date hierarchy, date is always being filtered so the ISFILTERED test is always true.  Was thinking about it over the weekend, and decided to try the following and it worked exactly as intended.

Targets = 
    IF(
        ISINSCOPE('Calendar'[Date]),
        BLANK(),
        IF(
            ISINSCOPE('Calendar'[MonthName]),
            SWITCH(
                TRUE(),
                AND(
                    SELECTEDVALUE('Calendar'[Year]) = 2020,
                    SELECTEDVALUE('Calendar'[MonthNumber]) = 4
                ), 796,

Etc...

But thank you for the suggestion!

[littlemojopuppy]

Would like to add...I'm willing to bet that there is some small, stupid oversight I'm making because I was looking at this for about four hours...