CASE WHEN THEN to create calculated column in table referring to disconnected table values PBI

[Anonymous]

Hello, Team!
I have some Tableau calculations that I need to migrate to Power BI, so I tried to find the similar methods in PBI DAX.

In Tableau I have the parameter that look like this:

Then, using this parameter the calculated column is created, and this column further is used in the filter pane, the calculation looks like this:

Important note that in Tableau the conditions are applied to COLUMNS, not measures, so all the mentioned names in orange square brackets are columns, and the name in pink color - is parameter.

the parameter above then is used in slicer, that looks like this:

So I think that approach in PBI is to create the disconnected table with ID and Category Name (the analog of parameter):

and then create calculated column in the main table using SWITCH (TRUE()) and SELECTEDVALUE() in order to address the categories in disconnected table:

But I have some troubles with condition statement, my measure returns only BLANK():

Test Col =
VAR Selection =
    SELECTEDVALUE ( 'Milestones To Show'[Category ID] )
VAR Next3Month =
    IF (
        'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 3,
        1,
        0
    )
VAR Next3Or6Month =
    IF (
        'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 3
            || 'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 6,
        1,
        0
    )
VAR YellowRed =
    IF (
        'CDP Master Plan'[Milestone Indicator] == "Yellow"
            || 'CDP Master Plan'[Milestone Indicator] == "Red",
        1,
        0
    )
VAR Next3MonthYellowRed =
    IF (
        'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 3
            || 'CDP Master Plan'[Milestone Indicator] == "Yellow"
            || 'CDP Master Plan'[Milestone Indicator] == "Red",
        1,
        0
    )
VAR Next3OR6MonthYellowRed =
    IF (
        'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 3
            || 'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] = 6
            || 'CDP Master Plan'[Milestone Indicator] == "Yellow"
            || 'CDP Master Plan'[Milestone Indicator] == "Red",
        1,
        0
    )
RETURN
    SWITCH (
        TRUE (),
        Selection = 1
            && Next3Month = 1, 1,
        Selection = 2
            && Next3Or6Month = 1, 1,
        Selection = 3
            && YellowRed = 1, 1,
        Selection = 4
            && Next3MonthYellowRed = 1, 1,
        Selection = 5
            && Next3OR6MonthYellowRed = 1, 1,
        BLANK ()
    )

So How can I reproduce this logic! Thanks for any help and recommendations!

[daXtreme]

Something like this... but to know exactly how to handle this one would have to know the exact schema of the model. Here's an attempt with the knowledge I have about the model (limited!).

[Your Measure] =
// Harvesting the value selected in the slicer. If only one value
// has been selected, it'll be returned through the var. If many or
// none, BLANK will be returned.
VAR selectedCatId = SELECTEDVALUE ( 'Milestones To Show'[Category ID] )
var selectedMilestoneFlag = selectedvalue ( 'CDP Master Plan'[Upcoming Milestone Flag (Next 3 and 6 Months)] )
var milestoneIndicator = selectedvalue ( 'CDP Master Plan'[Milestone Indicator] )
VAR Next3Month = selectedMilestoneFlag in { 3 }
VAR Next3Or6Month = selectedMilestoneFlag in { 3, 6 }
VAR YellowRed = milestoneIndicator in { "yellow", "red" }
VAR Next3MonthYellowRed = Next3Month || YellowRed
VAR Next3OR6MonthYellowRed = NextNext3Or6Month || YellowRed
var result =
SWITCH ( TRUE,
    selectedCatId = 1 && Next3Month, 1,
    selectedCatId = 2 && Next3Or6Month, 1,
    selectedCatId = 3 && YellowRed = 1, 1,
    selectedCatId = 4 && Next3MonthYellowRed = 1, 1,
    selectedCatId = 5 && Next3OR6MonthYellowRed = 1, 1
)
return
    result

[daXtreme]

Calculated columns in PowerBI are always STATIC. They can't change their values based on any selections you make during report execution. They are refreshed/recalculated only during data refreshes. This is the crucial observation to have in mind when working with calculated columns. For any dynamic calculations you always have to use measures.

[Anonymous]

Ok, I understand this point, thank you, then the question is how to implement this logic in measure, not in calculated column? The issue with how to recreate CASE WHEN THEN logic and connect this conditional calculation with values in disconnected table that can be used in the slicer is still remained unclear for me:)

[Anonymous]

Ok, got it, but still I cannot catch the idea how can I use the measure in slicer, or how can I achieve the situation in which I use the values from the disconnected table filter my visual based on the conditions mentioned above. So, finally the formula that I've attached need to return only 1 or 0, and I need both filtering the visual using this calculated column (measure?) and slicer with the mentioned categories.

[johnt75]

If you create the measure using the logic you have, wrapping each column reference inside SELECTEDVALUE(), then you can apply that measure as a filter on a table visual, or any other visual, to only show when the value is 1

[johnt75]

Calculated columns are only calculated during data refresh, so they don't take into account any slicers or filters. You'll need to implement a measure if you want it to be affected by filters or slicers