Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

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

‎06-20-2025 05:00 AM
I have Table with Should Cost, Should Cost Date, NewSupplier. I need to create a visualization Table, where it should show selected supplier values + values from Blank supplier where condition is that for blank supplier it should take the values, after maximum date for selected supplier for next 6 months. e.g. I ahve selected supplier from slicer like "ABC" and max date is 10-04-2023, then in the visualization table, it should reflect the values fro blank supplier next 6 months values from 10-04-2023.
I tired creating simple DAX, DAX Table and all. however it is not working. can some one help me here please. Please let me know if you need any thing from my end. Thanks! 
 
Filtered_ShouldCost =
VAR SelectedSupplier = SELECTEDVALUE(SupplierSlicer_01[NewSupplier])
VAR MaxDateForSupplier =
    CALCULATE(
        MAX('genai should_cost_data'[should_cost]),
        FILTER(
            ALL('genai should_cost_data'),
            'genai should_cost_data'[Supplier] = SelectedSupplier
        )
    )
VAR MinDate = MaxDateForSupplier
VAR MaxDate = EDATE(MaxDateForSupplier, 6)

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

VAR BlankRows =
    FILTER(
        ALL('genai should_cost_data'),
        'genai should_cost_data'[new_measure] = "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),
    IF(
        SelectedSupplier = "BlankSupplier",
        FILTER(
            ALL('genai should_cost_data'),
            'genai should_cost_data'[NewSupplier] = "BlankSupplier"
        ),
        UNION(SupplierRows, BlankRows)
    )
)
Labels:
Message 1 of 20
1,068 Views
0
1 ACCEPTED SOLUTION
v-echaithra
Community Support
Community Support
In response to Anonymous

‎07-02-2025 11:13 PM

Hi @Anonymous ,

Please refer to the file now that i have updated successfully.

View solution in original post

selected_value_max.pbix
Message 15 of 20
764 Views
0
19 REPLIES 19
v-echaithra
Community Support
Community Support

‎07-18-2025 05:32 AM

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.

Message 20 of 20
573 Views
0
v-echaithra
Community Support
Community Support

‎07-13-2025 10:22 PM

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.

Message 18 of 20
632 Views
0
Anonymous
Not applicable
In response to v-echaithra

‎07-13-2025 10:49 PM

Hello, Thanks for your inputs !! I was bit busyin some other work and could not verify the same. I will check and update here.

 

Thank you!

Message 19 of 20
628 Views
0
v-echaithra
Community Support
Community Support

‎07-09-2025 10:59 PM

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.

Message 17 of 20
661 Views
0
v-echaithra
Community Support
Community Support

‎07-07-2025 10:23 AM

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.

Message 16 of 20
732 Views
0
v-echaithra
Community Support
Community Support

‎07-02-2025 10:45 PM

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.

selected_value_max.pbix
Message 13 of 20
778 Views
0
Anonymous
Not applicable
In response to v-echaithra

‎07-02-2025 10:49 PM

Thank you!!

 

But this folder is empty 😞

 

Regards,

Anup

Message 14 of 20
776 Views
0
v-echaithra
Community Support
Community Support
In response to Anonymous

‎07-02-2025 11:13 PM

Hi @Anonymous ,

Please refer to the file now that i have updated successfully.

selected_value_max.pbix
Message 15 of 20
765 Views
0
v-echaithra
Community Support
Community Support

‎06-22-2025 09:30 PM

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.

Message 10 of 20
844 Views
0
Anonymous
Not applicable
In response to v-echaithra

‎06-22-2025 10:07 PM

Hello v-Echaithra,

 

Please find the below Data Set and Expected Output

 

SupplierCostDateCostCostDateFormatted
ABC Ltd01-01-202471.7301-01-2024
ABC Ltd01-02-202445.9602-01-2024
ABC Ltd01-03-202417.8303-01-2024
ABC Ltd01-04-202474.8104-01-2024
ABC Ltd01-05-202466.1405-01-2024
ABC Ltd01-06-202422.2106-01-2024
ABC Ltd01-07-202475.9607-01-2024
ABC Ltd01-08-202412.6608-01-2024
ABC Ltd01-09-202489.309-01-2024
ABC Ltd01-10-202439.8310-01-2024
ABC Ltd01-11-202429.4911-01-2024
ABC Ltd01-12-202427.912-01-2024
XYZ Corp01-01-202496.5101-01-2024
XYZ Corp01-02-202411.6402-01-2024
XYZ Corp01-03-202414.8503-01-2024
XYZ Corp01-04-202422.5804-01-2024
XYZ Corp01-05-202493.105-01-2024
XYZ Corp01-06-202415.7506-01-2024
XYZ Corp01-07-202461.5607-01-2024
XYZ Corp01-08-202411.0308-01-2024
XYZ Corp01-09-202487.1309-01-2024
XYZ Corp01-10-202432.1210-01-2024
XYZ Corp01-11-202422.6811-01-2024
XYZ Corp01-12-202416.5512-01-2024
BlankSupplier01-02-202451.9202-01-2024
BlankSupplier01-03-202474.2103-01-2024
BlankSupplier01-04-202451.3404-01-2024
BlankSupplier01-05-202466.4805-01-2024
BlankSupplier01-06-202465.3806-01-2024
BlankSupplier01-07-202461.0207-01-2024

 

Expected Output :

 

 Selected SupplierABC Ltd
   
SupplierCostDateCost
ABC Ltd01-01-202471.73
ABC Ltd01-02-202445.96
ABC Ltd01-03-202417.83
ABC Ltd01-04-202474.81
ABC Ltd01-05-202466.14
ABC Ltd01-06-202422.21
ABC Ltd01-07-202475.96
ABC Ltd01-08-202412.66
ABC Ltd01-09-202489.3
ABC Ltd01-10-202439.83
ABC Ltd01-11-202429.49
ABC Ltd01-12-202427.9
BlankSupplier01-02-202451.92
BlankSupplier01-03-202474.21
BlankSupplier01-04-202451.34
BlankSupplier01-05-202466.48
BlankSupplier01-06-202465.38
BlankSupplier01-07-202461.02
   
As Max Date for ABC Ltd is 01-12-2024
  
   
ABC LTdSum of(Should cost) =573.82
Blank SupplierMax date of ABC LTd from here Next 6 month entries to be considered370.35
   

 

Regards,

Anup

Message 11 of 20
833 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎07-07-2025 04:39 PM

Hi,

If you select 1-12-2024, then the next six months are from 1-1-2025 to 1-6-2025.  the dates of the blank supplier are not in that range.  Also, for the seected supplier why have you added all the numbers?  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 12 of 20
721 Views
0
Anonymous
Not applicable

‎06-20-2025 09:38 AM
Filtered_ShouldCost =
VAR SelectedSupplier = SELECTEDVALUE(SupplierSlicer_01[NewSupplier])
VAR IsBlankSupplier = SelectedSupplier = "BlankSupplier"
VAR IsRealSupplier = NOT ISBLANK(SelectedSupplier) && SelectedSupplier <> "BlankSupplier"
VAR MaxDateForRealSupplier =
    CALCULATE(
        MAX('genai should_cost_data'[should_cost_date]),
        FILTER(
            ALL('genai should_cost_data'),
            'genai should_cost_data'[NewSupplier] = SelectedSupplier
        )
    )
VAR BlankSupplierMinDate = MaxDateForRealSupplier
VAR BlankSupplierMaxDate = EDATE(MaxDateForRealSupplier, 6)

RETURN
    SWITCH(
        TRUE(),

        // Case 1: BlankSupplier only
        IsBlankSupplier,
        FILTER(
            ALL('genai should_cost_data'),
            'genai should_cost_data'[NewSupplier] = "BlankSupplier"
        ),

        // Case 2: Real Supplier selected
        IsRealSupplier,
        UNION(
            FILTER(
                ALL('genai should_cost_data'),
                'genai should_cost_data'[NewSupplier] = SelectedSupplier
            ),
            FILTER(
                ALL('genai should_cost_data'),
                'genai should_cost_data'[NewSupplier] = "BlankSupplier"
                &&
                'genai should_cost_data'[should_cost_date] >= BlankSupplierMinDate
                &&
                'genai should_cost_data'[should_cost_date] <= BlankSupplierMaxDate
            )
        ),

        // Case 3: Nothing selected
        ROW("NewSupplier", BLANK(), "Should_Cost_date", BLANK(), "Should_cost", BLANK())
    )
 
I tried this way as well, bust still getting the same error...
Message 9 of 20
906 Views
0
Anonymous
Not applicable

‎06-20-2025 07:29 AM

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

 

Message 8 of 20
970 Views
0
Nasif_Azam
Super User
Super User

‎06-20-2025 06:42 AM

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



Did I answer your question?
If so, mark my post as a solution!
Also consider helping someone else in the forums!

Proud to be a Super User!


LinkedIn
Message 6 of 20
988 Views
0
Anonymous
Not applicable
In response to Nasif_Azam

‎06-20-2025 07:28 AM

Thanks for your solution but it is not working and giving me the same error.

Message 7 of 20
970 Views
0
rajendraongole1
Super User
Super User

‎06-20-2025 05:13 AM

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)
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 3 of 20
1,050 Views
0
Anonymous
Not applicable
In response to rajendraongole1

‎06-20-2025 07:28 AM

Not working giving me the same error.

Message 5 of 20
970 Views
0
Anonymous
Not applicable
In response to rajendraongole1

‎06-20-2025 05:37 AM

So, you mean to say that I need not to create a claculated table, where in with the Measure only it should work?

 

Message 4 of 20
1,008 Views
0
Demert
Resolver III
Resolver III

‎06-20-2025 05:11 AM

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 

Demert_0-1750421368844.png

 

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 

Message 2 of 20
1,036 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All