Tableau LOD calculations to DAX with MAX and COUNTD:

[MohanVanks]

Hello All,

I am migrating one of tableau developed report to power bi and i am strugling to write a dax query measure for the below tableau formula.

sum({fixed [ProductLine], [CategoryNo],[Status]:max(TODAY() - [End Date])})
/
sum({fixed [ProductLine], [CategoryNo],[Status]:COUNTD([ECR No])})

Can anyone pleaase help me with the alternative dax expression for the above tableau formula.

Thanks in Advance.

Mohan V. 

[vanessafvg]

can you explain in business terms what you are trying to do so that one has an understanding of the expected definition.

[MohanVanks]

@vanessafvg  Basically, I am 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 which is 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.