Group BY

[shivambhat]

Expression.Error: We cannot convert the value true to type Text

[jbwtp · Accepted solution]

Hi @shivambhat, 

@ronrsnfld asked a very valid question :). Do you want to do OR or AND or something else? Also you are referring to a DAX formula, but the Errors and screenshots seems to be from Power Query.

This PQ code does OR on [Open], to make it ANDjust change List.Max to List.Min. Or do you need something else to happen on grouping (or do you need this in DAX)?

Table.Group(Source, {"Account"}, {{"Count", each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}})

Cheers,

John

[shivambhat]

This  works!  thank  you  so much for your  help 🙂

[shivambhat]

@jbwtp Thank  you  so much.  The  PQ code  which you   have  pasted works. 🙂

Thats what  I was looking for  🙂   @ronrsnfld Appereciate  you from taking  this up.

I  have started  to learn about Power Query  , Power BI & DAX.  This  is  all very  new to  me, also  would appereciate  you if  you guys  could   maybe  tell me  about   good resources  for Power BI, DAX & Power Query.Currently  I am just learning in BITS  & pieces from Youtube.

[shivambhat]

@jbwtp   Sorry I just came across   syntax issue for this-

Table.Group(#"Removed Columns", {"Account Name"}, {{"Open",each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}}), {{"Closed", each Logical.From(List.Max(List.Transform([Closed], each Number.From(Logical.From(_))))), type logical}})

Please have a look and help me rectifying this.

[jbwtp · Accepted solution]

Hi @shivambhat,

This is opening/closing brackets issue around this place: type logical}}), {{"Closed". At first sight, the formula should look like this:

Table.Group(#"Removed Columns", {"Account Name"}, {{"Open",each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}, {"Closed", each Logical.From(List.Max(List.Transform([Closed], each Number.From(Logical.From(_))))), type logical}})

I did not test it, but should work by the lok of it.

Kind regards,

John

[jbwtp · Accepted solution]

Hi @shivambhat, 

@ronrsnfld asked a very valid question :). Do you want to do OR or AND or something else? Also you are referring to a DAX formula, but the Errors and screenshots seems to be from Power Query.

This PQ code does OR on [Open], to make it ANDjust change List.Max to List.Min. Or do you need something else to happen on grouping (or do you need this in DAX)?

Table.Group(Source, {"Account"}, {{"Count", each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}})

Cheers,

John

[shivambhat]

Thanks  for your response.

I am totally new to Power BI  Space & I am  trying to  retrieve  data  for  accounts  which are  open & closed.

And Since  I have logical operators added in the  columns and hence  I am unable to   use Group  BY  operation & that is the  reason I used this  DAX  formula text.combine to get the output. But I believe  since it is not text  but a boolean, hence the error. Can you  please suggest me what  DAx formula  can  I use here? 

[ronrsnfld]

A small sample of your data and desired outcome from that data sample would be helpful.

[shivambhat]

The  above image is the  sample data  which  I need  for  accounts  column using group BY  feature.

The  Outcome  should  be  :

This  image should  be result  without   null values or error.  Value  should be as "True" which is a bolean.

[ronrsnfld]

Your sample data does not show any Accounts and only has 8 rows.  How do you know which lines go with which accounts?  

Perhaps you should look at the List.AllTrue function?  But a more realistic data sample might be helpful.

[shivambhat]

I cannot provide  you  the  dataset since its confidential.  But  for  your  understanding  I need to  know tw  DAX formula which will help me  capture  data  for  accounts  name  that  are open or closed.  Whereever  they  are open, it will show  as TRUE  &  wherever  it is  closed  it  will show as TRUE. And  Blank for  False instead of NULL.

[ronrsnfld]

Since you apparently feel that even with "made-up" data, any sample you provide would breach confidentiality, I have nothing to add beyond what is in my previous posting.  If that is not working, consider hiring a consultant and having him sign an NDA.

[ronrsnfld]

What are you trying to accomplish?  You code and error message makes it appear as if you are trying to combine the entries in a logical type column (containing either true or false) into a text string, and then typing the result as logical. That makes no sense.

A small sample of your data and desired outcome from that data sample would be helpful.