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.
Some help needed for a DAX statement.
I have a table with business units, a table containing text on monthly actuals, and a one-cell table visual in which I want the text to show related to the corresponding business unit.
The logic is:
So far I've managed to write the statement working for logic 1 and 2. For nr 3 I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".
My statement: Text=
IF (
ISFILTERED ( Businessunit[BU] );
IF (
HASONEFILTER ( Businessunit[BU] );
DISTINCT ( Comments[text] );
"select one business unit"
);
CALCULATE ( FILTER ( Businessunit; Businessunit[BU] = "Overall" ) )
)
Please help 🙂
Solved! Go to Solution.
The problem is in the final CALCULATE: the first argument of CALCULATE needs to be the expression to return, and the subsequent argument(s) are filters to apply.
Something like the below code should work.
It's probably not necessary in your model, but in the below code I also changed DISTINCT to SELECTEDVALUE, as it's a little safer. SELECTEDVALUE always returns a single value if there is only one value in the specified column, otherwise blank (or an optional second argument).
Text =
IF (
ISFILTERED ( Businessunit[BU] );
IF (
HASONEFILTER ( Businessunit[BU] );
SELECTEDVALUE ( Comments[text] );
"select one business unit"
);
CALCULATE ( SELECTEDVALUE ( Comments[text] ); Businessunit[BU] = "Overall" )
)
Regards
Owen
The problem is in the final CALCULATE: the first argument of CALCULATE needs to be the expression to return, and the subsequent argument(s) are filters to apply.
Something like the below code should work.
It's probably not necessary in your model, but in the below code I also changed DISTINCT to SELECTEDVALUE, as it's a little safer. SELECTEDVALUE always returns a single value if there is only one value in the specified column, otherwise blank (or an optional second argument).
Text =
IF (
ISFILTERED ( Businessunit[BU] );
IF (
HASONEFILTER ( Businessunit[BU] );
SELECTEDVALUE ( Comments[text] );
"select one business unit"
);
CALCULATE ( SELECTEDVALUE ( Comments[text] ); Businessunit[BU] = "Overall" )
)
Regards
Owen
Thanks Owen. Works like a charm!
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |