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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Nusc
Helper II
Helper II

Converting DAX to M

Hi,

 

I have the following statement that I need to convert from DAX to M in order to do subgroups in a matrix table:

 

YTDActual_C = IF(AND ('Billings (2)'[LineOfBusiness]="Construction", 'Billings (2)'[CustomerName] <> "" ) , Sum('Billings (2)'[Billed_Amount]))
 
Could anyone translate that for me?
 
Thank you,
1 ACCEPTED SOLUTION
edhans
Super User
Super User

Technically this will work @Nusc but there are issues with it:

if [LineOfBusiness]="Construction" and [CustomerName] <> "" then List.Sum(#"PreviousStepName"[Billed_Amount]) else null
  1. An if statement in PQ requires if/then/else, so I threw "else null" at the end.
  2. Comparing to "" might not work. "" is not the same as null. "" will be an empty "cell" with no data. A cell with null will actually show the word null in it in italics just like that. To compare to null, just use <> null vs <> ""
  3. Unlike DAX, Power Query has no filter context, so the List.Sum at the end will not  sum the field for the current row, it will provide the sum for the entire table. You would need a Table.SelectRows() in there to do that, but now you are getting into analysis and while I love Love LOVE Power Query, it is best at transformations, not analysis. So depending on your data set, DAX may be the way to go. But we'd need to see data to help.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User
Super User

Technically this will work @Nusc but there are issues with it:

if [LineOfBusiness]="Construction" and [CustomerName] <> "" then List.Sum(#"PreviousStepName"[Billed_Amount]) else null
  1. An if statement in PQ requires if/then/else, so I threw "else null" at the end.
  2. Comparing to "" might not work. "" is not the same as null. "" will be an empty "cell" with no data. A cell with null will actually show the word null in it in italics just like that. To compare to null, just use <> null vs <> ""
  3. Unlike DAX, Power Query has no filter context, so the List.Sum at the end will not  sum the field for the current row, it will provide the sum for the entire table. You would need a Table.SelectRows() in there to do that, but now you are getting into analysis and while I love Love LOVE Power Query, it is best at transformations, not analysis. So depending on your data set, DAX may be the way to go. But we'd need to see data to help.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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