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.
"Table 1" contains my data.
"Table 2" contains various combinations of filters, that I would like to use on my data, and then display "Table 2" on a matrix visual.
Picture below.
In excel, I can achieve this by a simple "=sumifs()" formula. How do I replicate this in DAX?
The closest comparable thread I found was this, but it doesn't offer a solution.
=filter() function does not work, because it requires you to specify a static filter, for example 'Mode of Transport' = "Ship". Instead, it should be dynamic, i.e. 'Table 1'[Mode of Transport] = 'Table 2' [Column G]
Using a simple =sum() formula and later applying filters on a matrix visual is also not an option, because filters have to be unique for each row, not for the entire visual. The above is dummy data; real data in Table 2 has thousands of filter combinations, hence creating a Matrix visual for each unique pair is not an option.
Appreciate the help!
Solved! Go to Solution.
Hi @Daniboy50 ,
According to your statement, I think you just need to create a calculated column in 'Table 2'.
Value Sum =
CALCULATE (
SUM ( 'Table 1'[Shipment Value] ),
FILTER (
'Table 1',
'Table 1'[Mode Of Transport] = EARLIER ( 'Table 2'[Mode of Transport] )
&& 'Table 1'[Fragile] = EARLIER ( 'Table 2'[Fragile] )
&& 'Table 1'[Priority] = EARLIER ( 'Table 2'[Priority] )
)
)
Result is as below.
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.
Thank you everyone for the help! I finally found the solution, using 'SelectedValue' formula.
Step 1: Create a new column in Table1, using 'SelectedValue' formula. This column, will return value from Table 2, column F, based on values in Table1, columns B,C & D.
Step2: Put this new column on a matrix visual.
This effectively allows you to filter data in Table1, by using very complex filters that are defined in Table 2.
Sorry if the original questions wasn't super clear!
Best,
Thank you everyone for the help! I finally found the solution, using 'SelectedValue' formula.
Step 1: Create a new column in Table1, using 'SelectedValue' formula. This column, will return value from Table 2, column F, based on values in Table1, columns B,C & D.
Step2: Put this new column on a matrix visual.
This effectively allows you to filter data in Table1, by using very complex filters that are defined in Table 2.
Sorry if the original questions wasn't super clear!
Best,
Hi,
I am not sure if I understood your question correctly, but if your desired output is creating a table, please check the below picture and the attached pbix file.
Result table =
ADDCOLUMNS (
CROSSJOIN (
ALL ( Data[Mode of Transport] ),
ALL ( Data[Fragile] ),
ALL ( Data[Priority] )
),
"@ValueSum",
SUMX (
FILTER (
Data,
Data[Mode of Transport] = EARLIER ( Data[Mode of Transport] )
&& Data[Fragile] = EARLIER ( Data[Fragile] )
&& Data[Priority] = EARLIER ( Data[Priority] )
),
Data[Shipment Value]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much! This is almost what I was looking for.
My goal isn't exactly to create the "Table 2". I already have a table which contains the information of specific combinations of filters I need. That information is stored in columns G,H and I. The only thing missing from that table, is the equivalent of "=sumifs()" formula in column J.
What you have created is a table with every possible combination of those filters, which is more than I wish to have. I would like to see the sums of only 3 specific combinations of filters shown in my example:
Hi @Daniboy50 ,
According to your statement, I think you just need to create a calculated column in 'Table 2'.
Value Sum =
CALCULATE (
SUM ( 'Table 1'[Shipment Value] ),
FILTER (
'Table 1',
'Table 1'[Mode Of Transport] = EARLIER ( 'Table 2'[Mode of Transport] )
&& 'Table 1'[Fragile] = EARLIER ( 'Table 2'[Fragile] )
&& 'Table 1'[Priority] = EARLIER ( 'Table 2'[Priority] )
)
)
Result is as below.
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.
Hi,
If you want to create a table that only shows three specific rows, please check the attached file and the below DAX formula for creating a table.
Result table =
FILTER (
ADDCOLUMNS (
CROSSJOIN (
ALL ( Data[Mode of Transport] ),
ALL ( Data[Fragile] ),
ALL ( Data[Priority] )
),
"@ValueSum",
SUMX (
FILTER (
Data,
Data[Mode of Transport] = EARLIER ( Data[Mode of Transport] )
&& Data[Fragile] = EARLIER ( Data[Fragile] )
&& Data[Priority] = EARLIER ( Data[Priority] )
),
Data[Shipment Value]
)
),
( Data[Mode of Transport] = "Truck"
&& Data[Fragile] = "No"
&& Data[Priority] = "No" )
|| ( Data[Mode of Transport] = "Plane"
&& Data[Fragile] = "Yes"
&& Data[Priority] = "No" )
|| ( Data[Mode of Transport] = "Train"
&& Data[Fragile] = "Yes"
&& Data[Priority] = "Yes" )
)
If you want to show the SUM in card visualization, then create a below measure and then put it into a card visualization.
Condition sum measure: =
CALCULATE (
SUM ( Data[Shipment Value] ),
FILTER (
Data,
( Data[Mode of Transport] = "Truck"
&& Data[Fragile] = "No"
&& Data[Priority] = "No" )
|| ( Data[Mode of Transport] = "Plane"
&& Data[Fragile] = "Yes"
&& Data[Priority] = "No" )
|| ( Data[Mode of Transport] = "Train"
&& Data[Fragile] = "Yes"
&& Data[Priority] = "Yes" )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |