Forum Discussion

mgrayTCB's avatar
mgrayTCB
Icon for Helper IV rankHelper IV
1 year ago
Solved

Why cant I remove the date filters?

I am trying to produce a measure that always returns the Current Month Occupied regardless of other filters (either on the visual or on the page). In the example below I would want to see 145 for Apr...
Need Help
  • kpost's avatar
    kpost
    1 year ago

    Alright, new fact table:

     

    Note the expected value of 2, specifically units "E" and "J"  ("E" is repeated twice -  we only want DISTINCT Unit IDs, and "I" is Vacant not Occupied even though it also falls in May)

     

     

     

     

    And here is the DAX, trying to mimic your existing code as closely as possible.

     

    Current_Month_Occupied =
    var dateToday = TODAY()
    var startDate = DATE(YEAR(dateToday), MONTH(dateToday), 1)
    var endDate = EOMONTH(dateToday, 0)

    return
        CALCULATE(
                COALESCE(COUNTROWS(DISTINCT(factsAffordableUnit[unitID])), 0),
                factsAffordableUnit[UnitStatus] = "Occupied",
                ALL(dimCalendar),
                dimCalendar[Date] >= startDate,
                dimCalendar[Date] <= endDate
        )
     
     
    (By the way, all coalesce does is default blanks to zero.  I use it out of habit on all of my numerical measures)
     
    See attached .pbix file.
     
    ///Mediocre Power BI Advice, but it's free///
kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
1 year ago

Alright, new fact table:

 

Note the expected value of 2, specifically units "E" and "J"  ("E" is repeated twice -  we only want DISTINCT Unit IDs, and "I" is Vacant not Occupied even though it also falls in May)

 

 

 

 

And here is the DAX, trying to mimic your existing code as closely as possible.

 

Current_Month_Occupied =
var dateToday = TODAY()
var startDate = DATE(YEAR(dateToday), MONTH(dateToday), 1)
var endDate = EOMONTH(dateToday, 0)

return
    CALCULATE(
            COALESCE(COUNTROWS(DISTINCT(factsAffordableUnit[unitID])), 0),
            factsAffordableUnit[UnitStatus] = "Occupied",
            ALL(dimCalendar),
            dimCalendar[Date] >= startDate,
            dimCalendar[Date] <= endDate
    )
 
 
(By the way, all coalesce does is default blanks to zero.  I use it out of habit on all of my numerical measures)
 
See attached .pbix file.
 
///Mediocre Power BI Advice, but it's free///
mgrayTCB's avatar
mgrayTCB
Icon for Helper IV rankHelper IV
1 year ago

I used your exact measure and I am still getting zeros.

 

Then I stripped our the start and end date logic and it magicly started to work. Other than the Coalese piece this is where I orginally started.

It works both with the countrows and the simple Unit Count measure.

Not sure what changed but it seems to be working.

 

Thanks for your help.