cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User I
Super User I

Re: Need help to solve dax measure

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
MarcelBeug Super Contributor
Super Contributor

Re: Need help to solve dax measure

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

Re: Need help to solve dax measure

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.

 

Anonymous
Not applicable

Re: Need help to solve dax measure

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.

Anonymous
Not applicable

Re: Need help to solve dax measure

@vanessafvg any help

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors