Aggregated lookup value for a base year

[Burneruser809]

Hi,

At the moment I have a lookup which takes the value for frequency from the table based on slicer selection. the lookup is the column 'Chosen_Value' and contains the DAX formula:

Chosen_Value =

VAR

lOOKUP_VALUE1 =

LOOKUPVALUE (

MoTiON[Frequency],

MoTiON[Model Year],

VALUES('Year'[Model Year]),

MoTiON[City],

VALUES(City[City]),

MoTiON[Option],

VALUES(Option[Option]),

1

)

RETURN

IF(HASONEFILTER('Year'[Model Year])&&HASONEFILTER(City[City])&&HASONEFILTER(Option[Option]), lOOKUP_VALUE1)

However at the moment i can only make a single slicer selection from each of the variables:

See examples below:

What i would like it to do

Ideally i want to be able to return a value for 'Chosen_Value' that can take multiple values across any of the slicers (essentially aggregating the individual selections).

An example of what i would expect for the slicer selection shown in the screenshot below which is a combination of the above two slicer selections is for it to return:

'Chosen_value' = 137850.24 

(essentially it should sum 47530.77 +90319.47 which were the two chosen values in the previous screenshots)

Attached file:

Ive also attached the pbix file if that helps as well.

https://drive.google.com/file/d/13yMeK_5xArQHaBdAeX6DW-eHKeDvXsX7/view?usp=sharing

Many thanks in advance!

[amitchandak]

@Burneruser809 , If you are trying to create a new column using a slicer filter. That will not work. You need to create a measure. Ideally, these slicers should be from the table or related table and there should be not need handle them unless they are from an independent table

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[Burneruser809]

Hi Amit,

The lookup currently works using the method described whereby the 'chosen_value' is a calculated measure.

However, it only works if a single options is selected in each of the slicer selections. i want to be able to select multiple options (or any combination of options from each slicer) and have the lookup perform an aggregation of the selected options and return that value back to me.

if you download my pbix file, you can see how if you click more than option in each slicer, the calculation breaks. i dont want this to happen, but insteaed the aggregated value to be calculated and returned 

[Anonymous · Accepted solution]

Hi @Burneruser809 ,

I download your sample file and have checked it. I know your requiremnt is to get 'Chosen_value' = 137850.24 ( 47530.77 +90319.47 ) when you select multiple selections in your slicer. This logic is incorrect. Firstly, you use lookupvalue function to catch the select [Frequency] value. Lookupvalue will return single value so when you select multiple selections in your slicers, your visual will show error. Here I suggest you to try this code.

Chosen_Value = 
VAR _SelectFreuency =
    CALCULATE (
        SUM ( 'MoTiON dates'[Frequency] ),
        FILTER (
            ALL ( 'MoTiON dates' ),
            'MoTiON dates'[Option]
                IN VALUES ( Option[Option] )
                    && 'MoTiON dates'[City]
                        IN VALUES ( City[City] )
                            && 'MoTiON dates'[Model Year] IN VALUES ( 'Year'[Model Year] )
        )
    )
RETURN
_SelectFreuency

You could get result by this measure based on multiple selections. However, the result is still not what you want.

You see we got value 3118656.16 not 137850.24. According to the logic of Power BI, it will combine all possible combinations of multiple selections. So, when you multiple select your slicer like above. Power BI will give filters like 2016/Barcelona/Choice A ,2016/Barcelona/Choice D, 2016/Roma/Choice A and so on. It won't just give you two determind combination.

So there are two workarounds for you.

1. If you will choose at most two more selections like in your screenshot, you can create duplicate Year, City and Option Slicers and calculate the second meausre and get result you want by measure1 + measure2. 

2. It is better for you to combine all key values like Year, City and Option to get a determind key value instead fuzzy one. 

You can create a calculated column by code as below.

Key value = COMBINEVALUES(" / ",'MoTiON dates'[Model Year],'MoTiON dates'[City],'MoTiON dates'[Option])

Then create an unrelated slicer table by this code.

Dim Key Value = VALUES('MoTiON dates'[Key Value])

Then update my code above

Chosen_Value =
VAR _SelectFreuency =
    CALCULATE (
        SUM ( 'MoTiON dates'[Frequency] ),
        FILTER (
            ALL ( 'MoTiON dates' ),
            'MoTiON dates'[Key Value] IN VALUES ( 'Dim Key Value'[Key Value] )
        )
    )
RETURN
    _SelectFreuency

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Burneruser809 , I am not sure about the expected output. I did some chance to model

Find the file after signature