- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculate Average Days
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: -
- STEP TO ( column from the same table) should be greater than STEP FROM
- STEP TO ( column from the same table) should be less than or equl to STEP TO (standalone slicer selected value coming from disconnected table)
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can anyone please help me on this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hoping someone could help me on this.

Helpful resources
User | Count |
---|---|
96 | |
94 | |
50 | |
45 | |
39 |