Calculate Average Days

[MohanVanks]

Hello All,

I am migrating tableau report to power bi, and we have a tableau expression with FIXED lod function.

The tableau expression is basically, calculating average days based on columns we have in our table.

Here is the sample data which i am working on.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZNbDoQwCEX30m9DePTFWoz738ao41BayXxYY86tF8hl3xNr6Zi2hIDI55uuj3I+jOnYHP+x9ofdxwIbEGfHnKDejqczeeMyY8IOquLtF8Vsv0AGVJ3sF4EAduMcc3NniQSch0PQQQUuNgEJSrgExQTRDATyGFKmQJEBpZkiKJMRqI9Bz2VoeyJgnU4ReHgYgYXNEfjCa4Q1ioAg39k4S8PineXFa/HuK5/cF0gMndpkvyoEumX/zsBboDo6kEDRgHWYBD1wgSo9+RgMhTyLMKLoUuA4R3tgPNqDcZnIepTg71d9+Z2P4wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EMPID = _t, DAYS = _t, #"STEP FROM" = _t, #"STEP TO" = _t, #"Status Order" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPID", Int64.Type}, {"DAYS", type number}, {"STEP FROM", Int64.Type}, {"STEP TO", Int64.Type}, {"Status Order", Int64.Type}})
in
    #"Changed Type"

Here, we have STEP FROM slicer which is a column coming from the above table data.

STEP TO is a slicer coming from standalone table which is disconnected to the above table.

STEP FROM and STEP TO contains the Numbers from 5 to 99 as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYzBDQAgCMR24e1DQDTOQth/DRPLh4brQaaE1Eg5f+oEZMZmBhzgHOc475AvSwEHQTNoRoccbNy9UvUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"STEP TO" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STEP TO", Int64.Type}})
in
    #"Changed Type"

So based on the STEP FROM and STEP TO selections

First We need to calculate the MAX of Days for each EMPID based on below conditions.

 Conditions: - 

1. STEP TO ( column from the same table) should be greater than STEP FROM
2. STEP TO ( column from the same table) should be less than or equl to STEP TO (standalone slicer selected value coming from disconnected table)
3. Status order should be greater than STEP TO (standalone slicer selected value coming from disconnected table)

expected output: - 

Any help on how can we achive this through measure in dax.

Thanks,

Mohan V.

[MohanVanks]

Can anyone please help me on this.

[MohanVanks]

Hoping someone could help me on this.