Data Linage and calculate on a column

[sbuster]

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

[v-yifanw-msft]

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.

2. 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.

2. 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.}