Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help to solve dax measure

Hello All,

I am trying to filter table values as below query using NewTable option in modeling tab.

 

 

Q1 = CALCULATETABLE(SUMMARIZE('Table','Table'[Col1],'Table'[Col2],'Table'[Col3],'Table'[Col4]','Table'[Col5]),FILTER('Table','Table'[Col1]="a"))

It gives me a perfect result as i expected in a table format.

 

 

likewise i have written 4 queries as i have the 4 conditions.

Instead of that i have tried to write all the conditions in single query as below.

 

Table = 
var Q1 = CALCULATETABLE(SUMMARIZE('Table','Table'[Col1],'Table'[Col2],'Table'[Col3],'Table'[Col4]','Table'[Col5]),FILTER('Table','Table'[Col1]="a"))

var Q2  = CALCULATETABLE(SUMMARIZE('Table','Table'[Col1],'Table'[Col2],,'Table'[Col4]'),FILTER('Table','Table'[Col1]="b"))

var Q3 = CALCULATETABLE(SUMMARIZE('Table','Table'[Col1],'Table'[Col3],'Table'[Col5]),FILTER('Table','Table'[Col1]="c"))

var Q4 = Q1 = CALCULATETABLE(SUMMARIZE('Table','Table'[Col1],'Table'[Col3],'Table'[Col4]'),FILTER('Table','Table'[Col1]="d"))

RETURN
SWITCH(
	TRUE(),
	VALUES('Table'[A])="a",Q1,
	VALUES('Table'[A])="b",Q2,
        VALUES('Table'[A])="c",Q3,	
        VALUES('Table'[A])="d",Q4,
)

 

But it is giving me the error as

 

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

 

Can anyone please suggest me to get this done.

 

Thanks 

MohanV

 

5 REPLIES 5
vanessafvg
Super User
Super User

@Anonymous what is the results of the tables, post screen shots





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg any help

Anonymous
Not applicable

Thanks for the reply @vanessafvg.

 

Here is the sample table

5.PNG

 

 

4.PNG

 

Likewise i am able to get the values for another 3 conditions as mentioned in my  question.

Can you please suggest me a way to get this done.

 

This is most probably a follow up on this thread in which I got involved.

 

From the followup questions (like this one), I start to suspect that 1 new table must be created with values from selected columns in the Source table (based on the value in a specific column)..

 

E.g. if the value in column A is ... then the required columns are:

Value "a": columns 1,2,3,4,5
Value "b": columns 1,2,4

Value "c": columns 1,3,5
Value "d": columns 1,3,4

empty: ???

 

But that is my interpretation, to be confirmed by @Anonymous

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Yes it is @MarcelBeug, but i think the issue that i have mentioned her is something regarding Dax measure if i am not wrong.

 

And yes it should create a table based on the value in specific column.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.