New (Calculated column like) Measure Column

[Anonymous]

We are using SSAS Tabular Model with Power BI for reporting purposes with Live Connection option.

The need is to create a report level flag/column so that the records can be supressed on the fly without creating the model based calculated columns. Attached is what I'm trying to do and is clearly not working!

(1) How to capture the "current row's" Case ID (any other column) in a variable so that I can use it in the formula? I tried the EARLIER/EARLIEST but it throws an error

(2) The need is to eliminate the current Case ID (as an example) all together if ever there is any instance of the coded text (any or all)

 So, as in the example above, this Case ID 620079 should be supressed from the listing.

All the relevant tables are properly joined and reports work just fine.

Thanks in advance,

[v-yuta-msft]

Hi ubaddala,

Early and Earliest can only be used in calculate column, not measure. in addtion, you said "The need is to eliminate the current Case ID (as an example) all together if ever there is any instance of the coded text (any or all)", which seems like different from the measure you have provided in screenshot, so could you please provide more details about your expected result and show the current measure you are using.

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy Tao,

Thanks for offering the help!

The measure shown in the original screen shot is an example of what I was trying to do. It is incomplete as I do not know how write it correctly! Idea is to grab the current record's Case ID and count the records in the lookup table to check for the string(s) occurence. SQL here: (note: if any one/all fact has the pattern, then the whole Case ID should be ignored)

Essentially what I'm trying to accomplish is:

1. Set the required filters such that the correct result set is returned

2. Out of the result set I want to further eliminate certain rows that meet the criteria. Psuedo logic is:

    (a). Flag the "current" row in the record set has any occurence of the strings (HQ REVIEW/RAC/GS REVIEW/DIVISION REVIEW), if so, eliminate that record from the output.
    (b). I understand that the Power BI (going against Tabular) does not allow to create a calculated column. Hence I was trying to create a measure that has a 0 (no occurence) or 1 and use that in the Visual Filter to eliminate the row from display.

The simplified model looks like:

Thanks,

Uday