Search code examples
powerbidaxslicers

PowerBI DAX Query - undo filter slicer on certain values based on expression

I have below table structure:

enter image description here

here I want to put a date slicer in Power BI to filter on dates and return the count of total rows as total in the card as shown below:

enter image description here

simple, the only twist is that I want to have the total of hybrid car added at all times.

i.e.

  • Brands before 5/25/2020 = 4 Hybrid + 1 Electric = 5
  • Brands before 12/5/2020 = 4 Hybrid + 3 Electric = 7

I have found a solution, which is creating a view in my database, which jus holds the number count of hybrid car (select count(*) from table where cartype = 'hybrid') and using it to sum with filter rows in power bi - but I am looking for a solution completely in Power BI DAX query.

any measure I have tried to create in power bi is filtered by date slicer and so doesn't work.

Solution

  • Create these measure:

    TOTALROWS = COUNT('cars'[brand]) 
ELECTRIC_NUM = CALCULATE([TotalRows],('cars'),'cars'[cartype]="ELECTRIC")
HYBRID_NUM   = CALCULATE([TOTALROWS],ALL('cars'),'cars'[cartype]="HYBRID")
TOTALBYBUSINESSLOGIC = CALCULATE([ELECTRIC_NUM]+[HYBRID_NUM])

    Now use the last measure (i.e. TOTALBYBUSINESSLOGIC) to be used in your Card to display the total, Notice the expression diffrence between ELECTRIC_NUM and HYBRID_NUM

    (In HYBRID_NUM I have used ALL, All will have it bypass the Date Slicer filter) whereas ELECTRIC_NUM will only proivde sum of rows falling in the active date sliver range.