Hey Folks. Good day ! Need help with a query for below mentioned criteria. The requirement is the count on promotion to be calculated based on the selection between two different timelines from the user. (it may be for one month or quarter,etc..) Employee Promotion table is as mentioned below. Emp NoJob LevelPeriod12311-Mar-2045621-Mar-2065411-Mar-2098731-Mar-2022351-Mar-2012311-Mar-2045631-Mar-2065421-Mar-2098731-Mar-2022361-Mar-2012321-Jun-2045631-Jun-2065421-Jun-2098731-Jun-2022351-Jun-2012321-Jul-2045641-Jul-2065421-Jul-2098731-Jul-2022361-Jul-20 Expected output is as follows: Emp NoJob LevelPeriodOutput12311-Mar-20 45621-Mar-20 65411-Mar-20 98731-Mar-20 22351-Mar-20 12311-Mar-20No Change45631-Mar-20Promoted65421-Mar-20No Change98731-Mar-20No Change22361-Mar-20Promoted12321-Jun-20Promoted45631-Jun-20No Change65421-Jun-20No Change98731-Jun-20No Change22351-Jun-20No Change12321-Jul-20No Change45641-Jul-20Promoted65421-Jul-20No Change98731-Jul-20No Change22361-Jul-20Promoted Check the above sample screen shot for requirement in power bi.Promotion or not to be calculated based on the selection of two filters (Dates) as available in the above snapshot

Forum Discussion

madhav2020

Frequent Visitor

6 years ago

Solved

DAX query to filter between two dates for a given crtiteria

Hey Folks.

Good day !

Need help with a query for below mentioned criteria.

The requirement is the count on promotion to be calculated based on the selection between two different timelines from the user. (it may be for one month or quarter,etc..)

Employee Promotion table is as mentioned below.

Emp No	Job Level	Period
123	1	1-Mar-20
456	2	1-Mar-20
654	1	1-Mar-20
987	3	1-Mar-20
223	5	1-Mar-20
123	1	1-Mar-20
456	3	1-Mar-20
654	2	1-Mar-20
987	3	1-Mar-20
223	6	1-Mar-20
123	2	1-Jun-20
456	3	1-Jun-20
654	2	1-Jun-20
987	3	1-Jun-20
223	5	1-Jun-20
123	2	1-Jul-20
456	4	1-Jul-20
654	2	1-Jul-20
987	3	1-Jul-20
223	6	1-Jul-20

Expected output is as follows:

Emp No	Job Level	Period	Output
123	1	1-Mar-20
456	2	1-Mar-20
654	1	1-Mar-20
987	3	1-Mar-20
223	5	1-Mar-20
123	1	1-Mar-20	No Change
456	3	1-Mar-20	Promoted
654	2	1-Mar-20	No Change
987	3	1-Mar-20	No Change
223	6	1-Mar-20	Promoted
123	2	1-Jun-20	Promoted
456	3	1-Jun-20	No Change
654	2	1-Jun-20	No Change
987	3	1-Jun-20	No Change
223	5	1-Jun-20	No Change
123	2	1-Jul-20	No Change
456	4	1-Jul-20	Promoted
654	2	1-Jul-20	No Change
987	3	1-Jul-20	No Change
223	6	1-Jul-20	Promoted

Check the above sample screen shot for requirement in power bi.

Promotion or not to be calculated based on the selection of two filters (Dates) as available in the above snapshot

Need Help

Show and Tell

Anonymous

6 years ago

is this what you expect?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xDoAgDEDRu3SGhFZAvYJXIAzu6GDi/UUdmlpkYPkhfW1KgDSAAazPod3Ww5KDbBL4EGsj1WPwzf/zNNY2qE7P/KB639VzXlfv03fjj3vPQbucu2ZFZlVkRkXmW0WWZJGk/2ZJljZZ9Jmc8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp No" = _t, #"Job Level" = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp No", Int64.Type}, {"Job Level", Int64.Type}, {"Period", type date}},"en-US"),
    change=(tab)=>
    let
    idx=Table.AddIndexColumn(tab,"i",0,1),    
    chg=Table.AddColumn(idx, "chg", each  try if [Job Level]>idx[Job Level]{[i]-1} then "Promoted" else "No Change" otherwise null) 
    in chg,


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp No"}, {{"grp", each change(_)}}),
    #"Expanded grp" = Table.ExpandTableColumn(#"Grouped Rows", "grp", {"Job Level", "Period", "chg"}, {"Job Level", "Period", "chg"})
in
    #"Expanded grp"

v-xuding-msft

6 years ago

Hi madhav2020 ,

If you want to implement it using DAX, you could try like this:

Create columns:

Add an index column in Query Editor

rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

Promotion =
VAR min_ =
    MAXX (
        FILTER (
            'Table',
            'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
                && 'Table'[rank] < EARLIER ( 'Table'[rank] )
        ),
        'Table'[rank]
    )
VAR joblevel =
    CALCULATE (
        MAX ( 'Table'[Job Level] ),
        FILTER (
            'Table',
            'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
                && 'Table'[rank] = min_
        )
    )
RETURN
    IF (
        'Table'[rank] > 1,
        IF ( 'Table'[Job Level] > joblevel, "Promoted", "No Change" ),
        "NULL"
    )

Create a new unconnected table and use its column as a between slicer.

Date = CALENDAR(MIN('Table'[Period]),MAX('Table'[Period]))

Create a measure to count Promotion.

count Promotion =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Promotion] = "Promoted"
            && 'Table'[Period] >= MIN ( 'Date'[Date] )
            && 'Table'[Period] <= MAX ( 'Date'[Date] )
    )
)

DAX query to filter between two dates for a given crtiteria.pbix52 KB

4 Replies

Anonymous

Not applicable

6 years ago

is this what you expect?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc8xDoAgDEDRu3SGhFZAvYJXIAzu6GDi/UUdmlpkYPkhfW1KgDSAAazPod3Ww5KDbBL4EGsj1WPwzf/zNNY2qE7P/KB639VzXlfv03fjj3vPQbucu2ZFZlVkRkXmW0WWZJGk/2ZJljZZ9Jmc8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp No" = _t, #"Job Level" = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp No", Int64.Type}, {"Job Level", Int64.Type}, {"Period", type date}},"en-US"),
    change=(tab)=>
    let
    idx=Table.AddIndexColumn(tab,"i",0,1),    
    chg=Table.AddColumn(idx, "chg", each  try if [Job Level]>idx[Job Level]{[i]-1} then "Promoted" else "No Change" otherwise null) 
    in chg,


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp No"}, {{"grp", each change(_)}}),
    #"Expanded grp" = Table.ExpandTableColumn(#"Grouped Rows", "grp", {"Job Level", "Period", "chg"}, {"Job Level", "Period", "chg"})
in
    #"Expanded grp"

polymathy2017
Advocate III
1 year ago
Of course not. The poster specifically states DAX query in the subject. This is not a DAX query and therefore does not answer the question.

amitchandak
Super User
6 years ago
madhav2020 , you need to have an index column in this case

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

column =
var _index = maxx(filter(table, [Emp] = earlier([Emp]) && [Period] <= earlier([Period]) && [index] <= earlier([index]) ) ,[index])
return
if(maxx(filter(table, [Emp] = earlier([Emp]) && [index] =_index),[Job Level]) -[Job Level] > 0, "Promoted", "No Change")

v-xuding-msft

Community Support

6 years ago

Hi madhav2020 ,

If you want to implement it using DAX, you could try like this:

Create columns:

Add an index column in Query Editor

rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

Promotion =
VAR min_ =
    MAXX (
        FILTER (
            'Table',
            'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
                && 'Table'[rank] < EARLIER ( 'Table'[rank] )
        ),
        'Table'[rank]
    )
VAR joblevel =
    CALCULATE (
        MAX ( 'Table'[Job Level] ),
        FILTER (
            'Table',
            'Table'[Emp No] = EARLIER ( 'Table'[Emp No] )
                && 'Table'[rank] = min_
        )
    )
RETURN
    IF (
        'Table'[rank] > 1,
        IF ( 'Table'[Job Level] > joblevel, "Promoted", "No Change" ),
        "NULL"
    )

Create a new unconnected table and use its column as a between slicer.

Date = CALENDAR(MIN('Table'[Period]),MAX('Table'[Period]))

Create a measure to count Promotion.

count Promotion =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Promotion] = "Promoted"
            && 'Table'[Period] >= MIN ( 'Date'[Date] )
            && 'Table'[Period] <= MAX ( 'Date'[Date] )
    )
)

DAX query to filter between two dates for a given crtiteria.pbix52 KB

Forum Discussion

DAX query to filter between two dates for a given crtiteria

4 Replies

Related content

Filter

Dynamic Filter

Inverse Filter

Override filters

Filter on Icons ?