Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MohanVanks
Helper II
Helper II

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

Can anyone please help me on this.

Hoping someone could help me on this.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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