Reply
MohanVanks
Helper II
Helper II
Partially syndicated - Outbound

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.

MohanVanks_0-1679978620164.png

 

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.

MohanVanks_1-1679978620097.png

 

 

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: - 

MohanVanks_0-1679985798578.png

 

 

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

 

Thanks,

Mohan V.

2 REPLIES 2
MohanVanks
Helper II
Helper II

Syndicated - Outbound

Can anyone please help me on this.

Syndicated - Outbound

Hoping someone could help me on this.

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)