Forum Discussion

sbuster's avatar
sbuster
Icon for Helper I rankHelper I
2 years ago

Data Linage and calculate on a column

I have a measure [Time in State (ISO)] where I am simply trying to convert a duration (in minutes) to ISO8601 display format (PTxDxHxM) so i can see the duration a particulate items was in a given state.  The measure itself is working fine but after further analysis I beleive it is breaking my data linage and therefore messing up the results in visual components.

 

Below is the result when I return a value without using expressions meausre (again, just for debugging pursposes the meausre simply returns sumx(State, State[Time In State]).  The row A is selected in the left table and the right table is filtered correctly.  Nothing special here.

 

This is the result when the measure returns a value using an IF expression to calculate the string to be returned.  Notice the Reco (right) table contains all rows in State table, and only the A rows have correct value from measure.  Here I would expect rows for B/C not to be displayed at all.  

 

I've learned the normal solution to this is to utilzie TREATAS to fix data linage problems, but here I am returning a totally different string value so I'm unclear where/how if TREATAS is even a solution for me.

 

 

Simplified table definitions are as follows:

Reco[ID]

State[RecoID, State, TimeInState]

 

There is a 1..Many relationship between Reco->State.

 

Measure[Time in State (ISO)]  = 
VAR v = CALCULATE(

            sumx(state,State[TimeInState])
            )
       
        VAR minutesInDay = 1440
        VAR minutesInHour = 60
        VAR d1 = IF(ROUNDDOWN(DIVIDE(v,minutesInDay),0)<=1,0,ROUNDDOWN(DIVIDE(v,minutesInDay),0))  ---breaks here
        VAR d1Mod = v - (d1*minutesInDay)
        VAR h1 = ROUNDDOWN(DIVIDE(d1Mod,minutesInHour),0)
        VAR h1Mod = d1Mod - (h1*minutesInHour)
        VAR r1 = "PT"&d1&"D"&h1&"H"&h1Mod&"M"
        return r1
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sbuster ,

     

    Based on the data you have provided, I have compiled two ways to solve the problem you are experiencing.

     

    Method one:

    Since you are using MEASURE, and when filtering with MEASURE, the two tables cannot contain a relationship.

    1. Remove the relationship between the two tables.

     

    1. Add a slicer.

     

    Method two:

    If you want to ensure the relationship between the two tables and do not want to add a slicer. you can change the measure to a column.

    1.Creat relationships.

     

    1. Change the measure to a column.

     

    Final output:

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your screenshot of the error, desired out put and pbix file without privacy information.

     

    Best Regards,

    Yifan Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.