Need to Parameterize 2 columns in the DAX measure calculations and slicer to select the param values

[Anonymous]

I am trying to create a measure using DAX,

scenario:
COMPARE_REVENUE = VAR SRC_REVENUE = CALCULATE(SUM([SRC_AMOUNT]),
                                                                        NOT(CONTAINSSTRING([REVENUE_TYPE],"CRA")),
                                                                       [FISCAL_QUARTER]="Q2FY23",
                                                                       [WEEK]="Wk5.4")
VAR TGT_REVENUE = CALCULATE(SUM([TGT_AMOUNT]),
                                  [FISCAL_QUARTER]="Q2FY23",
                                  [WEEK]="Wk5.4")
return
IF(SRC_REVENUE = TGT_REVENUE,"#00FF00","#FF0000")

---- This calculation is applied to a shape background color conditional formatting, so that if the values are matching it will display green color, otherwise it will show red.
But I need to parameterize the Fiscal Quarter and Week values and required as a slicer to get a dynamic report.
Fiscal Quarter = Q3FY22, Q4FY22, Q1FY23, Q2FY23.... Week = Wk1.1 to Wk12.4
Please give some inputs to get the appropriate result for this idea. Thanks!

[Anonymous]

Thank you, Yolo Zhu! I will try this method to fix this issue.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can refer to the following suggestion.

1.You can create two new table,e,g

Fiscal Quarter=SUMMARIZE('Table',[FISCAL_QUARTER])

Week=SUMMARIZE('Table',[WEEK])

Then put the columns of these two tables to slicers.

2.Then modify the measure

COMPARE_REVENUE =
VAR SRC_REVENUE =
    CALCULATE (
        SUM ( [SRC_AMOUNT] ),
        NOT ( CONTAINSSTRING ( [REVENUE_TYPE], "CRA" ) ),
        'Table'[FISCAL_QUARTER] IN VALUES ( 'Fiscal Quarter'[FISCAL_QUARTER] ),
        'Table'[WEEK] IN VALUES ( 'Week'[WEEK] )
    )
VAR TGT_REVENUE =
    CALCULATE (
        SUM ( [TGT_AMOUNT] ),
        'Table'[FISCAL_QUARTER] IN VALUES ( 'Fiscal Quarter'[FISCAL_QUARTER] ),
        'Table'[WEEK] IN VALUES ( 'Week'[WEEK] )
    )
RETURN
    IF ( SRC_REVENUE = TGT_REVENUE, "#00FF00", "#FF0000" )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.