Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
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)
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |