IF dax measure for different sharepoint lists and different columns

[Anonymous]

I have two sharepoint lists - Project KPIs and KPIStatusReport

The columns in each lists are as below:
Project KPIs:

- KPIs

- Basis of Linkages

KPIStatusReport

- KPIs

- Incremental Value

I want to write a dax measure based on the values submitted in the Basis of Linkages (Sum, Minimum, Maximum) for each KPI

eg. If basis of linkages is SUM, it should sum up incremental value for the KPI, if basis of linkages is Minimum it should display the minimum incremental value for the KPI

I have written the following measure but its not working:

Measure = CALCULATE(

SUM(PKPI_StatusReport[PKPISR_IncValue]

IF(

ProjectKPIs,

ProjectKPIs[PKPI_BasisofLinkages] = "Sum"

)

I am writing a measure in the table.

Can someone help me rectify this?

Regards,

Kavita

[v-juanli-msft]

Hi @Anonymous 

Measures

Mmax = CALCULATE(MAX(KPIStatusReport[Incremental Value]),FILTER('Project KPI','Project KPI'[Basis of Linkages]="maximum"))

Mmin = CALCULATE(MIN(KPIStatusReport[Incremental Value]),FILTER('Project KPI','Project KPI'[Basis of Linkages]="minimum"))

Msum = CALCULATE(SUM(KPIStatusReport[Incremental Value]),FILTER('Project KPI','Project KPI'[Basis of Linkages]="sum"))

Measure = SWITCH(MAX('Project KPI'[Basis of Linkages]),"sum",[Msum],"maximum",[Mmax],"minimum",[Mmin])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I tried adding measures differently for sum and max.

Measures for sum and max are named respectively - Max-BasisofLinkages and Sum-BasisofLinkages

I have created a new column to capture the values from the measures for each row item, they are named respective - Max-BoLValue and Sum-BoLValue

but its giving me the following error message:

A circular dependency was detected: PKPI_StatusReport[Column], PKPI_StatusReport[Max_BoLValue], PKPI_StatusReport[Column].

Regards,

Kavita

[Fowmy]

@Anonymous 

Check the table relationships. If you use my sample PBIX file and make adjustments as necessary, it should work. It is hard to explain without seeing your model.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Fowmy]

@Anonymous 

You can try:

Measure = 

VAR _KPI = SELECTEDVALUE(ProjectKPIs[PKPI_BasisofLinkages])

RETURN

SWITCH( _KPI,

 "SUM" = SUM(PKPI_StatusReport[PKPISR_IncValue]),
 "MIN" = MIN(PKPI_StatusReport[PKPISR_IncValue]),
 "MAX" = MAX(PKPI_StatusReport[PKPISR_IncValue]),
  0
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , Not very clear. Should be something like this

Measure = CALCULATE(
SUM(PKPI_StatusReport[PKPISR_IncValue]
filter(
ProjectKPIs,
ProjectKPIs[PKPI_BasisofLinkages] = "Sum"
))

[Anonymous]

Hi Amit,

Thank you for your reply. I had tried with filter, but it's not working.

Regards,

Kavita

[Fowmy]

@Anonymous 

I check it if you share the file with sample data. I did it based on my understanding of the question you posted, which was not clear enough.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Hi,

Thank you for your reply. The measure was accepted in my table but didn't retrieve any result value. So I am not sure where I am going wrong.

Sorry, the query was clear enough, trying to frame it again:

I have a SharePoint list connected to my power bi report - PKPIStatusReport 

This list captures information like KPI (looked up from ProjectKPI), Incremental Value

I have another list connected ProjectKPI

This list captures information like KPI, Basis of Linkages

So in my power bi report, I am trying to write a measure that will calculate Basis of Linkages Value based on incremental value submitted in PKPIStatusReport for each KPI based on the value provided in basis of linkages (Sum, Minimum, Maximum) in ProjectKPI list. The below table will give you an idea of what I am looking for

eg.  ProjectKPI:

KPI	Basis of Linkages
Cost reduction	SUM
Procurement	MAX

PKPIStatusReport

KPI	Status Date	Incremental Value	Basis of Linkages Value
Cost reduction	10 July 20	100	100
Cost reduction	12 July 20	100	200
Procurement	8 July 20	50	50
Procurement	11 July 20	70	70

Regards,

Kavita