Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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]
)
@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.
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
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!
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
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |