Forum Discussion

Shea_Nichols's avatar
Shea_Nichols
New Member
7 years ago
Solved

Filter Measure Based on Date in a Separate Table

Hello, I am fairly new to DAX & Power BI, so I hope I am not botching my terminology too badly in this question.

 

I am looking to make an "Inventory Liquidation" chart to show expected quantities over time into future dates. You might call it a burndown chart of sorts. 

 

Let's say the scenario is I am tracking several IT teams who need to migrate to Server 2016. I am starting with Table A which is simply one column of dates at monthly intervals, and Table B which you could water-down to 3 Columns:

- ServerName (unique)

- Team / Department (non-unique)

- Expected Migration / Shutdown Date

 

For each month in reference Table A, I want to know how many servers remain, i.e. Count of Migration Date later than Reference Date. I found a solution using a Calculated Column, but this seems to strip away the ability to then slice by Team:

ServersRemaining = COUNTX(
            FILTER(ServerList, ServerList[Migration_Date] > ref_MonthlyDates[ref_Date]),
            ServerList[Migration_Date]) + 0

I'm looking to beef this up to a Measure?, which would preserve the ability to filter with other columns. Thanks!

 

p.s. Some teams have not reported dates, so I am looking at another column/measure using COUNTBLANK.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Shea_Nichols

    To make your measure change with slicer, you could try this formula

    is filter = ISFILTERED(ServerList[Team])

ServersRemaining = 
VAR currentDate =
    SELECTEDVALUE ( ref_MonthlyDates[ref_Date] )
VAR countall =
    CALCULATE (
        COUNTX ( ServerList, ServerList[Migration_Date] ),
        FILTER ( ALL ( ServerList ), ServerList[Migration_Date] > currentDate )
    )
VAR countwithslcier =
    CALCULATE (
        COUNTX ( ServerList, ServerList[Migration_Date] ),
        FILTER (
            ALLEXCEPT ( ServerList, ServerList[Team] ),
            ServerList[Migration_Date] > currentDate
        )
    )
RETURN
    IF ( [is filter], countwithslcier, countall )

     

    Please note : before above, make sure you have unchecked the "make this relationship active" when editing relationship between ServerList and ref_MonthlyDates.

     

    Regarding using COUNTBLANK, you could try a similar way as above, if you have any question, please free to ask me.

     

    Best Regards

    Maggie

     

     

     

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Shea_Nichols

    To make your measure change with slicer, you could try this formula

    is filter = ISFILTERED(ServerList[Team])

ServersRemaining = 
VAR currentDate =
    SELECTEDVALUE ( ref_MonthlyDates[ref_Date] )
VAR countall =
    CALCULATE (
        COUNTX ( ServerList, ServerList[Migration_Date] ),
        FILTER ( ALL ( ServerList ), ServerList[Migration_Date] > currentDate )
    )
VAR countwithslcier =
    CALCULATE (
        COUNTX ( ServerList, ServerList[Migration_Date] ),
        FILTER (
            ALLEXCEPT ( ServerList, ServerList[Team] ),
            ServerList[Migration_Date] > currentDate
        )
    )
RETURN
    IF ( [is filter], countwithslcier, countall )

     

    Please note : before above, make sure you have unchecked the "make this relationship active" when editing relationship between ServerList and ref_MonthlyDates.

     

    Regarding using COUNTBLANK, you could try a similar way as above, if you have any question, please free to ask me.

     

    Best Regards

    Maggie

     

     

     

    • Shea_Nichols's avatar
      Shea_Nichols
      New Member
      7 years ago

      Thank you, Maggie! v-juanli-msft

       

      This solution is working exactly as desired! Just for feedback from my data in its current form, I made a minor tweak by including "+ 0" after the COUNTX() function to catch Teams not reporting a date for any of their servers. I was able to adapt it to add a second line counting blanks (pictured).

       

      I also did a slight "re-factor" to just move the ISFILTERED() function to the first arg of IF(), and removed the first line. It gave me an error as-copied, but that is probably more my misunderstanding than your code :).

       

      I think I will try next to have it reference a second field to also be able to slice by Manager/Director a level above the Team filter. Thanks for the inspiration!

       

      Cheers,

      Shea

  • DaFloDo's avatar
    DaFloDo
    Icon for Resolver I rankResolver I
    7 years ago

    hi Shea_Nichols,

     

    you could try a measure like this

    ServersRemaining = 
var currentDate = SELECTEDVALUE(ref_MonthlyDates[ref_Date])
return calculate(COUNTX(ServerList, ServerList[Migration_Date]), ALL(ServerList), ServerList[Migration_Date] > currentDate)

     

    regards

     

    florian