Switch between meassure

[oracledw]

Hello,

I have a grid with columns A, B, C and D with a single meassure

I want follwing behavior:

If GRID has only Column A then show meassure M1

If GRID has Column "A" with any other column(s) then show meassure M2

If grid does not have Column "A" then show meassure M2.

M1 = SUM(Salary)

M2 = SUM(Bonus)

Thanks.

[v-eqin-msft]

Hi @oracledw ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @oracledw ,

You could firstly unpivot other columns then use the following formula, or take a look at my pbix file here:

flag =
SWITCH ( 'CateSlicer'[Cate], "A", 0, 1 )

Measure =
VAR _sum =
    CALCULATE ( SUM ( CateSlicer[flag] ), ALLSELECTED ( CateSlicer[Cate] ) )
RETURN
    IF (
        _sum = 0,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Cat] = "Salary" )
        ),
        CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Cat] = "Bonus" ) )
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[DataZoe]

@oracledw You could try this measure:

Checked Measure =
VAR _a =
    SELECTEDVALUE ( Table[ColumnA] )
VAR _b =
    SELECTEDVALUE ( Table[ColumnB] )
VAR _c =
    SELECTEDVALUE ( Table[ColumnC] )
VAR _d =
    SELECTEDVALUE ( Table[ColumnD] )
RETURN
    IF (
        ISBLANK ( _a ) = FALSE ()
            && ISBLANK ( COALESCE ( _b, _c, _d ) ),
        [M1],
        [M2]
    )

[oracledw]

@DataZoeProblem is I only mentioned 4 columns, there could be 20-30 columns if not more user can bring to grid.

[negi007]

@oracledw  if there are 20-30 columns then you should try to unpivot all these columns and put them in one column. It will be much easier to filter value then. 

[negi007]

@oracledw  You can create a calculated measure like below. It will switch calculation basis the filter selection

Data_type_Filter_value =
IF(HASONEFILTER(Data_Type_Selected[Selection]),

Data_type_Filter_value =

SWITCH(SELECTEDVALUE(Data_Type_Selected[Selection]),

"Col A", SUM(salary),

"Col B", SUM(bonus),

"Col C", SUM(other),

"Col D", SUM(DA)

),
BLANK()
)

[Greg_Deckler]

@oracledw 

Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@oracledw , what is grid here , is it table or slicer. We can check the selected value and can change the measure calculation

example measure

if(selectedvalue(Table[Column]) ="A", [M1],[M2]_

See if this example matches on what you want -https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[oracledw]

@amitchandak It's a table. There are slicers on the report as well.

[amitchandak]

@oracledw , If Column A is a column (Column Name is A). I doubt it is possible.

If it is A value in column

Example

Column

-----------------

A

B

Then we can create a measure dependent on these values.

[oracledw]

@amitchandakNo A, B, C, D are column Names