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.
Hi,
I need to add some hidden fixed values to a filtered selection.
This s my table:
Account | Value |
A | 10 |
B | 22 |
C | 23 |
D | 44 |
Z | 45 |
Z | 56 |
Z | 78 |
Slicer with hidden Account eq to "Z", but I need to sum all Accounts Z values plus values of other selected accounts.
I hope it is harder to explain than to do it...
Thanks
Solved! Go to Solution.
Hi @Giadev ,
Is this what you want to get, when you select A at the slicer, you get the data of A plus the data sum of Z. If you select B at the slicer, you get the data of B plus the data sum of Z.If so,You could try the following dax to create a measure:
measure =
VAR sumz =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Account] = "Z" )
)
VAR TABLE2 =
CALCULATETABLE (
VALUES ( 'Table'[Account] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = SELECTEDVALUE ( 'Table'[Account] )
)
)
VAR last =
IF ( MAX ( 'Table'[Account] ) IN TABLE2, SUM ( 'Table'[Value] ) + sumz, "" )
RETURN
last
final you will get the below:
Wish it is helpful for you!
Best Regard
Lucien Wang
Hi @Giadev ,
Is this what you want to get, when you select A at the slicer, you get the data of A plus the data sum of Z. If you select B at the slicer, you get the data of B plus the data sum of Z.If so,You could try the following dax to create a measure:
measure =
VAR sumz =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Account] = "Z" )
)
VAR TABLE2 =
CALCULATETABLE (
VALUES ( 'Table'[Account] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = SELECTEDVALUE ( 'Table'[Account] )
)
)
VAR last =
IF ( MAX ( 'Table'[Account] ) IN TABLE2, SUM ( 'Table'[Value] ) + sumz, "" )
RETURN
last
final you will get the below:
Wish it is helpful for you!
Best Regard
Lucien Wang
@Giadev , as you mentioned, you can add a fixed sum up of Z to the selected account this way,
Hidden Z =
CALCULATE ( SUM ( Table1[Value] ), Table1[Account] = "Z" ) + SUM ( Table1[Value] )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Giadev , better you create an independent Account table without values of Z
summarize(filter(Table, Table[Account] <>"Z"),Table[Account])
now you can create a new measure
Measure =
var _sel = allselected([Account])
return
calculate(sum(Table[Value]), filter(Table, Table[Account] = "Z" || Table[Account] in _sel))
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |