Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help to convert dax query into M Query

Hi Team,  @tamerj1

Thanks for helping in the below mentioned question.

Solved: Re: Need Help with DAX Command - Microsoft Power BI Community

below is the formula which is working fine .

CYCQCW-Win Rev =
VAR CurrentFCST = MAX ( 'Trend'[FCST] )
VAR CurrentWeek = MAX ( 'Trend'[WEEK_NUM] )
VAR CurrentStage = MAX ( 'Trend'[SS_No])
VAR CurrentValue = MAX ( 'Trend'[REV$M] )
VAR MaxWeek = CALCULATE ( MAX ( 'Trend'[WEEK_NUM] ), ALLEXCEPT ( 'Trend', 'Trend'[FCST] ) )
RETURN
IF (
CurrentFCST = "CQ" && CurrentWeek = MaxWeek && CurrentStage IN { "07", "08" },
CurrentValue,
0
);

 

As per my requirement i have to create a chart which shows the data in dynamic hierarchy , and as this formula is written in dax i cannot view this column in editor , can you please help me in changing it to M.
Thanks & Regards
Shiv Kumar

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 

    Here you go

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Tamer\Dropbox\Tamer - Personal\Work\PBI Community\Community 2\Community 112.xlsx"), null, true),
    Trend_Sheet = Source{[Item="Trend",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Trend_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"FCST", type text}, {"WEEK_NUM", Int64.Type}, {"SS_No", Int64.Type}, {"REV$M", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "NEW REV$M", each if [FCST] = "CQ" and ( [SS_No] = 7 or [SS_No] = 8 ) and [WEEK_NUM] = List.Max (Table.SelectColumns(Table.SelectRows(#"Changed Type", each ([FCST] = "CQ")), {"WEEK_NUM"} )[WEEK_NUM]) then [#"REV$M"] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"NEW REV$M", type number}})
in
    #"Changed Type1"

     

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    Here you go

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Tamer\Dropbox\Tamer - Personal\Work\PBI Community\Community 2\Community 112.xlsx"), null, true),
    Trend_Sheet = Source{[Item="Trend",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Trend_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"FCST", type text}, {"WEEK_NUM", Int64.Type}, {"SS_No", Int64.Type}, {"REV$M", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "NEW REV$M", each if [FCST] = "CQ" and ( [SS_No] = 7 or [SS_No] = 8 ) and [WEEK_NUM] = List.Max (Table.SelectColumns(Table.SelectRows(#"Changed Type", each ([FCST] = "CQ")), {"WEEK_NUM"} )[WEEK_NUM]) then [#"REV$M"] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"NEW REV$M", type number}})
in
    #"Changed Type1"

     

  • sujana_pbi's avatar
    sujana_pbi
    New Member
    2 years ago

    Hello, could anyone help me with my task? 

    below is one how-to do in m query.

    VAR Maxval =

        MAX ( Sheet1[length] )

    VAR IND = Sheet1[Index] + 1

    VAR EXPN =

        LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] + 1 )

    VAR FNL =

        Sheet1[length]

            - LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] - 1 )

    VAR FNL1 =

        Sheet1[length]

            - LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] + 1 )

    RETURN

        IF (

            Sheet1[length] = Maxval

                || (FNL < 0 && Sheet1[length]<> 0)

                || ISBLANK ( EXPN )

                || FNL1 = 0 && Sheet1[length] in {5,10,15,20,25,30}

                &&(FNL1=0 )

                ||(FNL=0&&EXPN=0),1,0)