Search code examples
daxfilteringpowerbi-desktop

How to find out if more than one column is filtered

Let's assume I have a table called Campaigns and RangeCampaigns, I have written one DAX code which gets a total using that Campaigns table and another one getting a total from the RangeCampaigns table.

What I want to do is, if the Campaign slicer has been filtered on its own it should show Measure 1, however, if its not filtered (or it's filtered alongside any other filter) it should show Measure 2

enter image description here

Example: If Campaign and range_group were filtered it should return the Measure 2 value (Total Avg Bays)

Current Code:

testCode = 
IF ( ISFILTERED('Campaign Table'[campaign]) = TRUE(),
    SUMX (
        SUMMARIZE ( range_plans, range_plans[branch] ,range_plans[range_name], range_plans[number_bays]),
        range_plans[number_bays]
    ),
    [Total Avg Bays])

Issue:

When range_group is filtered it does not change, but I don't want to explicitly check if that slicer is filtered I want to dynamically check if any other column is being filtered to determine which value to show

Solution

  • Try something like:

    testCode = 
  var onlyCampaignFiltered =
    ISFILTERED('Campaign Table'[campaign]) &&
    NOT CALCULATE(ISCROSSFILTERED('range_plans'), REMOVEFILTERS('Campaign Table'[campaign]) )

  RETURN IF(onlyCampaignFiltered,
    SUMX (
      SUMMARIZE ( range_plans, range_plans[branch] ,range_plans[range_name], range_plans[number_bays]),
      range_plans[number_bays]
    ),
    [Total Avg Bays]
  )