Filter context playing up with Calculated column logic

[sivarajan21]

Hi Team,

I have a calculated column as below:

Its working fine in this table level. But when I use this column in a table visual in report its not working correctly and its giving 'Yes' in all rows of the table for same deviceid: FYR, screenshot attached(Will be happier if this logic is pushed into Power query for better performance):

It should contain only rows where there are warnings(Yes).

But when i remove the OpenMinutes,Storetime and Door Time from above table, I get the correct output:

My expected output columns for filtered sitecode 73 and deviceid 2452 is i need below columns along with Store Time, Door Time and OpenMinutes.

Store Time comes from Openstateduration table DateTimeRounded which is linked to Door table DateTimeRounded(Both date columns should match):

Door Time will be coming from Createdon date of Openstateduration table which is linked to Door table Createdon(Both date columns should match):

Modeling for these tables are:

Could you please help me fix this issue? Also, much appreciated if you help me with alternative or better solution like pushing Warning logic to power query or creating better modeling for this issue etc.

PFA file here B&M (4).pbix

Please feel free to let me know if any issues!

Thanks in advance!

@Ahmedx @marcorusso @tharunkumarRTK @v-zhengdxu-msft @ray_aramburo 

[ray_aramburo · Accepted solution]

Hi @sivarajan21  the issue further than the measures lies in the semantic model design. You have multiple "Yes" due to ambiguous relationship tha the context has. Example: You have a 02/27/2025 9:30:00 AM date which has N rows due the Updates column, the N number of stores and N number of Devices, that's ok, it's a fact table, but then, the Warnings table comes from another mini facts table itself as it has a list of N number of devices which have N number of logs. If you have as an example 

Log ID	Store ID	Device ID	DateRounded	Update Date
1	17	5000	02/27/2025 9:30:00 AM	02/27/2025 9:35:00 AM
2	17	5000	02/27/2025 9:30:00 AM	02/27/2025 9:45:00 AM
3	17	5000	02/27/2025 9:30:00 AM	02/27/2025 9:55:00 AM

And then you have a log for the warnings of devices such as (see table below)

ID	Device ID	Date Rounded	Closed On	Warning
34	5000	02/27/2025 9:30:00 AM	02/27/2025 9:36:00 AM	Yes
35	5000	02/27/2025 9:30:00 AM	02/27/2025 9:38:00 AM	No

What will happen is that since the device log doesn't have a store key for those specific times, when producing the results, the model doesn't know how to distribute the resulting values, thus, repeating and spreading them across the visual. To solve this you need a composite key involving Store/Site - Device ID - Time - Log ID .

Additionally, you are creating measures for situations that would be best addressed upstream or directly in Power Query such as calculating the Site Name, getting the Breach tying the store dates and device dates. 

[sivarajan21]

Hi @ray_aramburo ,

Sir  it means a lot to me and i will close this query

I have tried as you said and it helped me atleast for now to get a solution

My output:

As per your suggestion i created a key column combined them using Siteid-Deviceid-Createdon datetime-Logid 

then created one-one between both

Many thanks sir!

[sivarajan21]

Hi @ray_aramburo sir,

Thanks for your quick response and taking time to look into it! one need a lot of patience to look into the community and solve others problem. Hats off!

You are my only hope to solve this scenario! I need to submit this report by EOD today. 

But am confused how to achieve the below sir:

"To solve this you need a composite key involving Store/Site - Device ID - Time - Log ID .

Additionally, you are creating measures for situations that would be best addressed upstream or directly in Power Query such as calculating the Site Name, getting the Breach tying the store dates and device dates"

Please can you help on how to achieve the below?

You small help is much appreciated

Thanks in advance!

@ray_aramburo @marcorusso @tharunkumarRTK 

[sivarajan21]

Good Day! 

Just to confirm, have you had a chance to look into this? I know you might be busy but if you can help me with above its much appreciated.
As my clients have given a deadline for tonight, I would greatly appreciate your quick insights on resolving this issue. Your expertise would be immensely helpful in finding a solution
The issue I am facing is that my report visual should contain only rows where there are warnings ("Yes") and not other rows. I don't why it gives yes in all rows.

many thanks in advance!
@marcorusso @tharunkumarRTK @ray_aramburo @v-zhengdxu-msft 

[amitchandak]

@sivarajan21 , A calculated column can be dynamic. So max of CreatedOn is max in table,

if you want, you can create measure. Than max will based on data in avaiable because grouping of visual row filter. You can move around that using

calculate(Max(Table[CreatedOn]), all(Table))

or

calculate(Max(Table[CreatedOn]), allselected(Table))

or

calculate(Max(Table[CreatedOn]), filter(all(Table), Table[DeviceId] = Max(Table[DeviceId]) ) ) 

[sivarajan21]

Hi @amitchandak ,

Thanks for your quick response!

But to be frank, I didn't understand how to apply your measure concept in my case.

Can you please help?

Thanks in advance!