swicth with Slicer Selection

[Anonymous]

Hi Experts

Not sure on how to do the following, when ALL is shown on the filter/slicer i want to use measure from Table A and when the user selects a product from filter/slicer i want to use meaure from Table B

Measure from Table A

KPY

PrimaryYears

Measure from Table B

KMYears

PrimaryYears

[MFelix]

Hi @Anonymous ,

Your information is very reduced but based on what I can understand what you need to do is something similar to this:

Measure A or B =
IF (
    DISTINCTCOUNT ( Table[SlicerColumn] )
        = CALCULATE ( DISTINCTCOUNT ( Table[SlicerColumn] ); ALL ( Table[SlicerColumn] ) );
    [Measure Table A];
    [Measure Table B ]
)

Without having more information is dificult to point out a better answer but this should do the trick.

Adjust the measure based on your tables, columns and measure names.

Regards,

MFelix

[Anonymous]

Hi MFelix

Ill upload a file with some sample data, i have called your measure All Measure and its in table STEMBRANDYEARS

The filter i have highlighted in Blue.

Table A is called KM Year and i want to use measure MYKMYears and if possible also PRIMARYTOOUTCOMEYEARS when all is selected

and

TableB is called STEMBRANDYEARS and i want to use measure KPYears and if poosible from this table PRIMARYTOOUTCOMEYEARS  when a product is selected from the highlighted filter.

https://www.dropbox.com/s/80akom988tzyz0k/Test_KPV_1.1.pbix?dl=0

[MFelix]

Hi @Anonymous ,

Believe you have upload incorrect file as you can see there is no All Measure and no highlithed filter:

Regards,

MFelix

[Anonymous]

apologies...

see new link

https://www.dropbox.com/s/2bdk6y5kl7ovsry/Test_KPV_1.1.pbix?dl=0

[MFelix]

Hi @Anonymous ,

To what I can see the issue is that this measure is not formated as percentage as the other two.

I have formatted as % and place the 3 measures on the chart and the numbers where matching when making a selection.

Can you please format measure as percentage, place the other two measures on the visual and then make the selection on the filter to see if they are matching?

Regards,

MFelix

[Anonymous]

MFelix

CAnyou kinldy re send bak or upload the pbix file, kinldy please.

[MFelix]

Hi @Anonymous,

See file attach, although you don't see the All Measure on the visual because the other surpass it if you over on a single data point you can see on the tooltip that the values are the same depending on the selection for one or the other measure.

Regards,

MFelix

[Anonymous]

HI Mfelix

Apologies if i my have confused you. But When All is selected i want the measure from Table KM Years i.e. KM Years and PRIMARYTOOUTCOMEYEARS to be shown in the graph not else. only one line and like wise when a product is selected the measure from STEMBRANDYEARS, then that line only...and the lines for ALL and the individal product should match the red line grph to the left.

[MFelix · Accepted solution]

Hi @Anonymous ,

Sorry but I'm not understanding your phrase.

In the example I send you I only place the 3 measures for comparision., if you take out the other two measure you only bet the red line that get's one or other calculation.

check PBIX file attach if this is the expected result.

Regards,

MFelix

[Anonymous]

Hi MFelix

This is not the except result. the except result is the grapht to the left. we can get he expect result if somehow we can use a switch function where when ALL is shown on the slicer we use mesure from

KM Years

MYKMYears and

PRIMARYTOOUTCOMEYEARS

if you add those above to the Axis PRIMARYTOOUTCOMEYEARS and Vales MYKMYears for ALL

give you the result as per graph on the left.

And when the user select a Product like Birmingham Head ReSection Stem" from filter then we replace Value with MYKMYears and Axis with PRIMARYTOOUTCOMEYEARS from the STEMBRANDYEARS table. and this match once again with the graph on the left.

[MFelix]

I cannot see the chart on the left because it's an R visual and I don't have that installed on my machine.

Can you post images of both results you need?

Regards,
MFelix

[Anonymous]

Result when measure from KM Years are selected.

Results when user selects a product from filter use measure from table STEMBRANDYEAR

[Anonymous]

Hi MFelix

Any lucky with the measure and or switch function

[MFelix]

Hi @Anonymous,

Your issue is with context I'm checking the rest of the model and preparing the answer will post it tomorrow or Monday.

Regards,
MFelix

[MFelix]

Hi @Anonymous ,

As refered your situation is refering to context. When you have two measure that you want to place in the same chart they need to be linked in any manner, and the axis of the visual needs to be inline with both measures.

On your case you are making the calculation based on the PRIMARYOUTCOMEYEARS however on both measures you use the column from each table so since you don't have any common field between them when you place it on a chart using one of the columns (in your case the STEMBRANDYEARS the othe measure gives you a straight line since the context isn't found.

What I did was to create a relationship (inactive) between the PRIMARYOUTCOMEYEARS of both tables then made the following changes:

MYKMYears v2 = 
 1-(CALCULATE(
                PRODUCT(
                    'KM Years'[1-e_i/d_i]);
                            FILTER(ALLSELECTED(
                                'KM Years'[PRIMARYTOOUTCOMEYEARS]); ISONORAFTER('KM Years'[PRIMARYTOOUTCOMEYEARS];MAX('KM Years'[PRIMARYTOOUTCOMEYEARS]);DESC))))


KPYears v2 =
1
    - (
        CALCULATE (
            PRODUCT ( 'STEMBRANDYEARS'[1-e_i/d_i] );
            FILTER (
                ALLSELECTED ( 'KM Years'[PRIMARYTOOUTCOMEYEARS] );
                ISONORAFTER (
                        'KM Years'[PRIMARYTOOUTCOMEYEARS]; MAX ( 'KM Years'[PRIMARYTOOUTCOMEYEARS] ); DESC
                )
            );
            USERELATIONSHIP ( 'KM Years'[PRIMARYTOOUTCOMEYEARS]; STEMBRANDYEARS[PRIMARYTOOUTCOMEYEARS] )
        )
    )


All Measure = IF (
    DISTINCTCOUNT ( STEMBRANDYEARS[STEMBRAND] )
        = CALCULATE ( DISTINCTCOUNT ( STEMBRANDYEARS[STEMBRAND]  ); ALL ( STEMBRANDYEARS[STEMBRAND]  ) );
    [MYKMYears v2];
    [KPYears v2]
)

Although I'm getting the correct result between the visuals there are two errors that I keep getting on the KPYears measure:

• The graph does not align the Y-axis below 100%
• And you have values that have blank values or a single point in prymaryoutcome the values are going to the top or to the bottom of the chart.

Believe that this issues are because of the way your data is structured, and the filters used on the that are not matching the context of the calculations.

Can you explain why you have the tables setup as they are?

Check PBI file attach

Regards,

MFelix

[Anonymous]

Hi MFelix. Firstly thank you very much for all your time a mind effort let me review the pbix and I'll come back to you shortly.