Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Prince Kumar]

Hi Guys,

I have a table that is having some divisions values named tableA(this is used in matrix)

Bakery

Camera

Food

Household

Chairs

Fans

I have target table ,that is having targets based on each division for this year

Division	Target_0	Target_50	Target_100	Target_150	Year
Bakery	0.36	0.38	0.41	0.46	2022
Camera	0.52	0.54	0.56	0.61	2022
Food	0.48	0.5	0.52	0.57	2022
Household	0.36	0.39	0.42	0.48	2022

I have measure that is having these values and i need to check if divisions(table A) is present in division of target table then match it’s corresponding rows to measure

if the measure is less target_50 column then true else false.

I created measure but this works only for one row, i was trying to compare tableA[Division] with ‘Target table'[Division] directly through related also ,it isn’t working

VAR CHECK_RED=IF (FIRSTNONBLANK((tableA[Division]), 1) =FIRSTNONBLANK(('Target table'[Division]), 1)  &&
FIRSTNONBLANK(('Yearly Targets'[Year]), 1)="2022",
IF(FORMAT('View By'[Measure],"Percent")<FORMAT(FIRSTNONBLANK(('Target table'[Target_50_Percent]),1),"Percent"),True,False),False)
RETURN 
CHECK_RED

Could u please guide me how we can compare 2 columns here? Just need comparison for 2 columns

——————————
Prince Kumar
Consultant- Cloud Data Engineer
——————————

[Kaz Shakir]

,

It would be easier to answer your question if you post a .pbix file with a sample of your data and your data model.

Based on what you described, I tried to create what I think your data and model look like.?  I think you have three tables like so:
Division:

Target:

Results:

And the data model might look like this:

In that case, you could add two calculated columns to the Divisions table, that would have formulas like this:

Does Division Have Target_50 = 
    NOT(ISBLANK(RELATED(target[Target_50])))




Are results for division less than Target_50 = 
    IF(
        divisions[Does Division Have Target_50],
        IF(RELATED(results[Value]) < RELATED(target[Target_50]), "True", "False"),
        BLANK()
    )?

And then you can create a table visual to show the results:

Hope that helps.  Please consider posting your ,pbix file, and we will be able to give you a more customized answer.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————
——————————————-