powerbidaxpowerbi-desktoppower-platform

In Power BI, can I create variables and then use them to filter a matrix?


I have been tasked with recreated an Access database in Power Apps. For the metrics in my database, I am delving into PowerBI for the first time. I want the new solution to run the same, if not better, than the original.

In the original, the user would input a start date and an end date, then click a button to save those dates. From there, they could select one of three buttons to display the results of a corresponding query, each query using those dates slightly differently. Note that each query groups records similarly to a pivot table.

Let's say I have a list of famous people throughout history that includes their birth and death dates. The user selects a date range and clicks button number one. The metric table is then filtered for everyone on our list who was born in that date range. The user clicks button number two without adjusting the date range. The metric table is now filtered for everyone on our list who died in that date range. Lastly, the user clicks button number three without adjusting the date range. The metric table is now filtered for everyone who is alive as of the ending date of the date range.

I was able to figure out how to create a date picker with a date table and then create variables using the two selected dates. I then figured out how to create radio buttons and identify which button is selected in another variable. So I have three variables: "Start", "End", and "Report".

From here I was thinking I could somehow use those variables to dictate how the matrix is filtered (i.e., If "Report" = "Born", filter matrix for birth dates between "Start" and "End". If "Report" = "Died", filter matrix for death dates between "Start" and "End". If "Report" = "Alive", filter for birth dates before "End", death dates either null or after "End"). This is where I've gotten stuck. Not sure if this is the best approach, or if it's even possible.


Solution

  • Don't think of it as filtering the matrix but more of how to calculate the values.

    You can create a measure similar to:

    Person count = 
      var dateS = MIN(DateTable[Date])
      var dateE = MAX(DateTable[Date])
    
      var bornCount =
        CALCULATE(
          COUNTROWS('FactDataTable'),
          REMOVEFILTERS('DateTable'),
          dateS <= 'FactDataTable'[Birth Date] && 'FactDataTable'[Birth Date] <= dateE
        )
    
      var deathCount =
        CALCULATE(
          COUNTROWS('FactDataTable'),
          REMOVEFILTERS('DateTable'),
          dateS <= 'FactDataTable'[Death Date] && 'FactDataTable'[Death Date] <= dateE
        )
    
      var aliveCount =
        CALCULATE(
          COUNTROWS('FactDataTable'),
          REMOVEFILTERS('DateTable'),
          'FactDataTable'[Birth Date] <= dateE &&
          ( 'FactDataTable'[Death Date] > dateE || ISBLANK('FactDataTable'[Death Date]) )
        )
    
      return SWITCH( SELECTEDVALUE('Report Table'[Report]),
        "Born": bornCount,
        "Died": deathCount,
        "Alive": aliveCount
      )
    

    Use this new measure in your Matrix Values. You can also add the 'Report Table'[Report] to the Columns of your Matrix and this will add a nice touch of updating the column name as the selection changes.