filtering row level data after slicer selection

[JimJim]

Hi Guys,

I have a really complicated problem here that has stumped me for a few days now. I have created a test report so that I can show you my issue: agreements_test.pbix

On my report, I am trying to show 2 measures:

Selected Product Value (value of selected product)

Other Product Value (value of all other key products)

The caveat is that Other Product Value should only calculate for agreements that also have a product in the slicer. So if a user selects Generator then Other Product Value should calculate the value of all other products but only for agreements that also have a generator, otherwise it should return blank.

I'm aware that I can apply a measure as a filter but I really need to filter the row level data before the measure calculates. Is this even possible?

[kpost · Accepted solution]

If I understand correctly, you want the 'other' to total all non-selected key products, but only for agreements that do contain the selected product, for example if generator is selected, it will total all NON-generator items but only for agreements containing generators.  If the result is 0, return Blank instead.

I think this will do it for you.  I have also attached the Power BI file.

Total_All_Non-Selected_But_Agreement_Contains_Selected =

var selected_product = SELECTEDVALUE('product'[product])

var table1 = CALCULATETABLE(agreements, ALL('product'[product]), 'product'[key_product] = 1)

var table2 = SUMMARIZE(

                table1,

                [agreement_number],

                "@selected_sum", CALCULATE(COALESCE(SUM(agreements[agreement_value]), 0), 'product'[product] = selected_product),

                "@not_selected_sum", CALCULATE(COALESCE(SUM(agreements[agreement_value]), 0), 'product'[product] <> selected_product)

                )

VAR RESULT =

    COALESCE(SUMX(table2, IF([@selected_sum] > 0, [@not_selected_sum], 0)), 0)

RETURN IF(RESULT = 0, BLANK(), RESULT)

///Mediocre Power BI Advice, but it's free///

[JimJim]

@kpost awesome job 😎

My next step is to recreate the solution on my main report and see how it scales.