Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
6 years ago
Solved

Stumped! Get Row Header Value

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!

  • 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!

     

3 Replies

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion

      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's avatar
    littlemojopuppy
    Community Champion

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