Power BI: Compare Selected Year with Table Column and Check for Blank Value

[Basil-Ur-Rehman]

give me the solution of the scenario in which I need to get selected value from the slicer by which reflects the planning year. i need to compare , subtraction of 1 of selected Year with the column of PMOKPIManagement named as Planning year if condition become true check the column of same table named as ParentKPIId is blank then write "New" else "Forwarded" in power bi ask this question with microsoft community'
Following are the Calculated column DAX:
StatusCheckMeasure =
VAR SelectedYear = SELECTEDVALUE(PMOKPIManagement[PlanningYear]) // Get the selected year from the slicer
RETURN
IF (
NOT(ISBLANK(SelectedYear)) && (SelectedYear - 1) = PMOKPIManagement[PlanningYear], // Check if selected year - 1 matches PlanningYear
IF(ISBLANK(PMOKPIManagement[ParentKPIId]), "New", "Forwarded"), // Check if ParentKPIId is blank
"New"
)

Following are Measure DAX:
StatusCheckMeasure =
VAR SelectedYear = SELECTEDVALUE('YourSlicerTable'[PlanningYear], 0) // Set default value to 0 if no selection or multiple selections
RETURN
IF (
SelectedYear = 0, // If no valid selection, return "No Selection"
"No Selection",
IF (
(SelectedYear - 1) = PMOKPIManagement[PlanningYear], // Check if selected year - 1 matches PlanningYear
IF(ISBLANK(PMOKPIManagement[ParentKPIId]), "New", "Forwarded"),
"New"
)
)
Desired output is KPIs of selected year are forwarded or new on the basis of ParentKPIId and previous year

[v-karpurapud]

Hi @Basil-Ur-Rehman 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.

[v-karpurapud]

Hi @Basil-Ur-Rehman 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @Basil-Ur-Rehman 

Thank you for submitting your question to the Microsoft Fabric Community Forum.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[Basil-Ur-Rehman]

attached are the records of 2026 so it should be reflected as "forwarded" due to slicer`s value is 2027, or else if I change the slicer to 2026 it will reflect "new" 

[VahidDM · Accepted solution]

Use a measure that reads the slicer year and classifies each row:

StatusCheck :=
VAR SelYear = SELECTEDVALUE('YearTable'[PlanningYear]) -- slicer
RETURN
SWITCH(
TRUE(),
ISBLANK(SelYear), BLANK(),
PMOKPIManagement[PlanningYear] = SelYear
&& ISBLANK( PMOKPIManagement[ParentKPIId] ), "New",
PMOKPIManagement[PlanningYear] = SelYear
&& NOT ISBLANK( PMOKPIManagement[ParentKPIId] ), "Forwarded",
PMOKPIManagement[PlanningYear] = SelYear - 1, "Forwarded",
BLANK()
)

If the row is in the selected year and ParentKPIId is blank ⇒ New.

If the row is in the selected year and ParentKPIId is not blank ⇒ Forwarded.

If the row is from previous year (SelYear-1) ⇒ Forwarded.

To count:

New KPIs :=
CALCULATE(
DISTINCTCOUNT(PMOKPIManagement[KPIId]),
FILTER(ALLSELECTED(PMOKPIManagement), [StatusCheck] = "New")
)

Forwarded KPIs :=
CALCULATE(
DISTINCTCOUNT(PMOKPIManagement[KPIId]),
FILTER(ALLSELECTED(PMOKPIManagement), [StatusCheck] = "Forwarded")
)

Use StatusCheck in the table (or the two count measures in cards).

[FBergamaschi]

Hi @Basil-Ur-Rehman,

what is your question? Is your DAX code not woring correctly? Can you show the issue?

Furthermore, if you want a fast solution,

please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[Basil-Ur-Rehman]

yes, as I put dax on calculated column it cannot have selected dropdown values and when i put in measure table column aren`t available

[FBergamaschi]

Sorry but I do not understand

Please show images and provide same data as I aske din the previous post

Thanks