Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all
I have measure formula like
divide(SUM('Table1'[Col1]),SUM('Table1'[Col2])+SUM('Table2'[Col3]) ,0)
Problem is with 'Table2'[Col3] field - it should be excluded from formula in case if some attribute, Let's say 'Table3'[Attr1] are added to grouping or filtered.
I tried using SELECTEDVALUE():
VAR compValue = SELECTEDVALUE(Table3'[Attr1],"Total")
RETURN
SWITCH(
compValue,
"Total", divide(SUM('Table1'[Col1]),SUM('Table1'[Col2])+SUM('Table2'[Col3]) ,0),
divide(SUM('Table1'[Col1]),SUM('Table1'[Col2]) ,0)
)
but it works well for grouping - when only one or several values of 'Table3'[Attr1] selected.
But in case if there are no grouping by 'Table3'[Attr1] and data are filtered by this attribute it doesn't work well. Because according to requirements it also should calculate as divide(SUM('Table1'[Col1]),SUM('Table1'[Col2]) ,0) , but it doesn't since there are selected more than one Table3'[Attr1] values .
Is there any way to detect, that there are any filter by some attribute added to report?
@cyberirbis , Try like
VAR compValue = maxx(filter(allselcted('Table3'),'Table3'[Attr1]= "Total"),'Table3'[Attr1])
RETURN
SWITCH(
compValue,
"Total", divide(SUM('Table1'[Col1]),SUM('Table1'[Col2])+SUM('Table2'[Col3]) ,0),
divide(SUM('Table1'[Col1]),SUM('Table1'[Col2]) ,0)
)
sorry, I don't get what this expression for:
VAR compValue = maxx(filter(allselcted('Table3'),'Table3'[Attr1]= "Total"),'Table3'[Attr1])
This will not work at all since 'Table3'[Attr1] is integer
In my example
VAR compValue = SELECTEDVALUE(Table3'[Attr1],"Total")
"Total" it's not value of 'Table3'[Attr1] field, it's just value to identify total row with several 'Table3'[Attr1] values for further using in switch function
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |