The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

This error is cause of you returning multiple values or a table. In your return statement you are returning an union between two tables. A dax meassure can never return a table. You can check the output of this dax measure in the DAX Query view 

This dax query view can return output tables for virtual tables and will help you debug what you need.

To return 1 value from a virtual you need to use a minx, sumx,maxx,... function 

Hi Anonymous  - If you'd rather keep your visual tied to the original table and apply filtering through a measure, let me know — we can do it with a TRUE/FALSE filter measure using similar logic

check the below modified logic and let know.

Filtered_ShouldCost =
VAR SelectedSupplier = SELECTEDVALUE(SupplierSlicer_01[NewSupplier])
VAR MaxDateForSupplier =
CALCULATE(
MAX('genai should_cost_data'[should_cost_date]),
FILTER(
ALL('genai should_cost_data'),
'genai should_cost_data'[NewSupplier] = SelectedSupplier
)
)
VAR MinDate = MaxDateForSupplier
VAR MaxDate = EDATE(MaxDateForSupplier, 6)

VAR SupplierRows =
FILTER(
ALL('genai should_cost_data'),
'genai should_cost_data'[NewSupplier] = SelectedSupplier
)

VAR BlankSupplierRows =
FILTER(
ALL('genai should_cost_data'),
ISBLANK('genai should_cost_data'[NewSupplier]) &&
'genai should_cost_data'[should_cost_date] >= MinDate &&
'genai should_cost_data'[should_cost_date] <= MaxDate
)

RETURN
IF (
ISBLANK(SelectedSupplier),
FILTER('genai should_cost_data', FALSE),
UNION(SupplierRows, BlankSupplierRows)
)

Hey Anonymous ,

It seems like you're trying to create a table that displays the values for the selected supplier and, for a "BlankSupplier," the values from the next 6 months based on the maximum date for the selected supplier. However, you're facing issues with the DAX expression returning multiple columns when a scalar value is expected.

Try the DAX expression:

Filtered_ShouldCost =
VAR SelectedSupplier = SELECTEDVALUE(SupplierSlicer_01[NewSupplier])
VAR MaxDateForSupplier =
    CALCULATE(
        MAX('genai should_cost_data'[should_cost_date]),
        FILTER(
            ALL('genai should_cost_data'),
            'genai should_cost_data'[NewSupplier] = SelectedSupplier
        )
    )
VAR MinDate = MaxDateForSupplier
VAR MaxDate = EDATE(MaxDateForSupplier, 6)

VAR SupplierRows =
    FILTER(
        ALL('genai should_cost_data'),
        'genai should_cost_data'[NewSupplier] = SelectedSupplier
    )

VAR BlankRows =
    FILTER(
        ALL('genai should_cost_data'),
        'genai should_cost_data'[NewSupplier] = "BlankSupplier"
        && 'genai should_cost_data'[should_cost_date] >= MinDate
        && 'genai should_cost_data'[should_cost_date] <= MaxDate
    )

RETURN
IF(
    ISBLANK(SelectedSupplier),
    FILTER('genai should_cost_data', FALSE),
    UNION(SupplierRows, BlankRows)
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Can some one help me here it is bit urgent please !!

Hi Anonymous ,

Thanks for reaching out to Microsoft Community and explaining your requirement. To help reproduce the issue and provide an accurate solution, could you please share a small sample of your dataset and the expected output?

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,
Chaithra E.

Hi Anonymous ,

Thanks for providing the sample data.

Create a Disconnected Table for Supplier Slicer.
Go to Modeling > New Table:
SupplierList =
DISTINCT (
FILTER (
VALUES(ShouldCostData[Supplier]),
ShouldCostData[Supplier] <> "BlankSupplier"))

Add a Slicer using this table.
Use SupplierList[Supplier] in the slicer.

Then create a Measure (BlankSupplier_measure)

BlankSupplier_measure =
VAR SelectedSupplier = SELECTEDVALUE(SupplierList[Supplier])

VAR MinDate =
CALCULATE (
MIN (ShouldCostData[CostDate]),
FILTER (
ALL (ShouldCostData),
ShouldCostData[Supplier] = SelectedSupplier
)
)
VAR EndDate = EDATE(MinDate, 6)

RETURN
CALCULATE (
COUNTROWS (ShouldCostData),
FILTER (
ShouldCostData,
(ShouldCostData[Supplier] = SelectedSupplier) ||
(ShouldCostData[Supplier] = "BlankSupplier" &&
ShouldCostData[CostDate] >= MinDate &&
ShouldCostData[CostDate] < EndDate )))

Now you create a Table visual and add this measure to your table visual.

Apply a visual-level filter: BlankSupplier_measure > 0

Please refer to the solution provided in the pbix file.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

Hi Anonymous ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

Hi Anonymous ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

Hi Anonymous ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.