Which method to use to lookup value from filtered table

[FelipeF]

Hello,

I am trying to figure out the best method. I believe I need to use LOOKUPVALUE but I am missing something. I have a slicer for the user to filter Table2. Then there is another slicer to select Category from Table1. The desired result is the corresponding cell in Count 1 returned in a Card based on filtered data from Table1 and Table2.

Table 1

Slicer1	Name	Channel	Count 1
Cat A	Name A		2
Cat B	Name A		4
Cat C	Name A		6
Cat D	Name A		8
Cat A	Name B		2
Cat B	Name B		4
Cat C	Name B		6
Cat D	Name B		8
Cat A	Name C	Ch A	3
Cat B	Name C	Ch A	5
Cat C	Name C	Ch A	6
Cat D	Name C	Ch A	7
Cat A	Name C	Ch B	5
Cat B	Name C	Ch B	7
Cat C	Name C	Ch B	6
Cat D	Name C	Ch B	5

Table2

Name	Channel	Slicer2
Name A	Ch A	apple
Name A	Ch B	banana
Name A	Ch A	orange
Name B	Ch B	grapes
Name B	Ch C	plums

For example, if the user selects 'Cat A' from Slicer1 in Table1 and 'apple' from Slicer2 in Table2 the result should be Count 1 = 3.

Another example, if the user selects 'Cat B' from Slicer1 in Table1 and 'grapes' from Slicer2 in Table2 the result should be Count 1 = 7.

The tables are not joined. I want to do the same type of filtering for other columns as well. What approach do you suggest? Thank you.

[v-deddai1-msft]

Hi FelipeF,

As a complement to Greg_Deckler method, If your slicer can be multi-selected, Please use the following dax:

max= 
   MAXX(
        FILTER(ALL(Table1),
           AND(Table1[Slicer1] IN VALUES(Table1[Slicer1]),Table1[Channel] in VALUES(Table2[Channel]))),
        Table1[Count 1])
sum= 
   SUMX(
        FILTER(ALL(Table1),
           AND(Table1[Slicer1] IN VALUES(Table1[Slicer1]),Table1[Channel] in VALUES(Table2[Channel]))),
        Table1[Count 1])

You can also refer  to the pbix file:   https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV4cu995filEjDZ9Ib...

Best Regards,

Dedmon Dai

[Greg_Deckler]

I suppose you might be getting hung on on the filtering that is occuring when slicing, you could do this:

Measure = 
  MAXX(
    FILTER(
      ALL('Table 1'),
      'Table 1'[Slicer1] = SELECTEDVALUE('Table 1'[Slicer]) && 
        'Table 1'[Channel] = SELECTEDVALUE('Table 2'[Channel])
    ),
    [Count]
  )

[FelipeF]

@Greg_Deckler Thank you, this is great! Really appreciate your assisstance. I believe this is almost exactly what I need. I want to add other rows for Count 1 to the total for Count 1 for each unique value in Name. How might I do that? Right now I could see creating a MAXX formula for each Name but that seems unnecessary. 

For example, if the user selects 'Cat A' from Slicer1 in Table1 and 'apple' from Slicer2 in Table2 the result should be Count 1 = 7 (sum of Count 1 for Name A, Name B, and Name C given slicer selections). I have other columns that I want to filter for the other names, but not necessarily slicers.

[v-deddai1-msft]

Hi FelipeF,

According to your description, would you please try to use the following measure to compute the total for each unique value in Name:

sum = CALCULATE(SUM(Table1[Count 1]),FILTER(ALLSLECTED(Table1),Table1[Channel] in VALUES(Table2[Channel]) || Table1[Channel] = BLANK()))

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

Best Regards,

Dedmon Dai

[FelipeF]

@v-deddai1-msft 

Thank you, I really appreciate the help. That formula is resulting in an error, "A single value for column 'Channel' in table 'Table2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I'll try to explain better and add context. Note that I changed from needing to calculate by Name to Group.

Table 1

Slicer1	Name	Group	Channel	Slicer3	Fruit	Count 1
Cat A	Name A	Group 1		No		2
Cat A	Name A	Group 1		Yes		3
Cat B	Name A	Group 1		No		4
Cat C	Name A	Group 1		No		6
Cat D	Name A	Group 1		Yes		8
Cat A	Name B	Group 1		No		2
Cat A	Name B	Group 1		Yes		5
Cat B	Name B	Group 1		No		4
Cat C	Name B	Group 1		No		6
Cat D	Name B	Group 1		No		8
Cat A	Name C	Group 2	Ch A	No		3
Cat A	Name C	Group 2	Ch A	No	apple	1
Cat A	Name C	Group 2	Ch A	No	orange	1
Cat B	Name C	Group 2	Ch A	No		5
Cat C	Name C	Group 2	Ch A	No		6
Cat D	Name C	Group 2	Ch A	No		7
Cat A	Name C	Group 2	Ch B	No		5
Cat B	Name C	Group 2	Ch B	No		7
Cat C	Name C	Group 2	Ch B	No		6
Cat D	Name C	Group 2	Ch B	No		5

Table2

Name	Channel	Slicer2
Name A	Ch A	apple
Name A	Ch B	banana
Name A	Ch A	orange
Name B	Ch B	grapes
Name B	Ch C	plums

EXAMPLE:

The user selects:

Slicer1 = CatA

Slicer2 = Apple

Slicer3 = Yes

Meaure returns sum of Count 1 by unique Group = 

For Group 1: Lookup Name from Table2 based on Slicer2 (Name A). Lookup Category in Table1 based on Slicer1 (CatA). Lookup Yes in Table1 from Slicer3 (Yes). The result for Count 1 = 3 (the bolded row for Group 1 in Table1 above).

+

For Group 2: Lookup Channel from Table2 based on Slicer2 (Ch A). Lookup Category in Table1 based on Slicer1 (CatA). Lookup Fruit based on Slicer2 (apple). The result for Count 1 = 1 (the bolded row for Group 2 in Table1 above).

So I'm thinking about it like a bunch of vlookups across the two tables referencing various slicers.

Thank you!

[v-deddai1-msft · Accepted solution]

Hi @FelipeF ,

I create three calculation table for use as slicer1, slicer2, slicer3, make this three slicer as parameters

slicer1 = VALUES(Table1[Slicer1])

slicer2 = Table2

slicer3 = VALUES(Table1[Slicer3])

create measure returns sum of Count 1 by unique Group:

Measure =

VAR a =

    CALCULATE (

       SUM ( Table1[Count 1] ),

        FILTER (

            Table1,

            Table1[Slicer1] IN VALUES ( slicer1[Slicer1] )

                && Table1[Slicer3] IN VALUES ( slicer3[Slicer3] )

                && Table1[Name] IN VALUES ( slicer2[Name] )

        )

    )

VAR b =

    CALCULATE (

        SUM ( Table1[Count 1] ),

        FILTER (

            Table1,

            Table1[Slicer1] IN VALUES ( slicer1[Slicer1] )

                && Table1[Channel] IN VALUES ( slicer2[Channel] )

                && Table1[Fruit] IN VALUES ( slicer2[Slicer2] )

        )

    )

RETURN

    a + b

Please refer to the pbix file  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZ1L-c7uEYBIru0rZX...

Best Regards,

Dedmon Dai

[FelipeF]

@v-deddai1-msft Thank you, this is exactly what I need. I really appreciate people like you. 

If you have time, can you explain the use of parameters here?