Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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,
Need Help
  • edhans's avatar
    edhans
    5 years ago

    Technically this will work Anonymous 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.

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Technically this will work Anonymous 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.