Can't we create 2 measures out of a measure and a column based on two measures.

[vkomarag]

Hi All,

I have the below data

ID	year	question	rating
1	2015	q1	1
2	2015	q1	3
3	2015	q1	4
4	2015	q1	2
1	2016	q1	1
2	2016	q1	2
3	2016	q1	5
4	2016	q1	4
1	2017	q1	5
2	2017	q1	4
3	2017	q1	5

I have a measure calculated based on some rating column(say some customized formula) : Qfactor

Now I create two measure based on Qfactor

PREV_YEAR=CALCULATE(QFACTOR,FILTER(SHEET1,SHEET1[YEAR]="2015")

CURRYEAR=CALCULATE(QFACTOR,FILTER(SHEET1,SHEET1[YEAR]="2016")

Now i have to calculate  one indicator column(not measure; because i need to relate this indiactor to another source)

Indicator=if(curr_year>prev_year,2,if(curr_year<prev_year,1,0))-------------------------

Here is my problem.I end up in circular dependency when i create indicator column. I can create measure and absolutely no problem but i cant create a relationship to another source(we can create only columns)..

I am working on this for the past couple of days. Can anyone please help.

Thanks

KVB

[Greg_Deckler]

You should see if you can create calculated columns that duplicate your measures and base your final Indicator column on those columns instead of the measures to avoid circular dependencies. 

You can still have your measures that you could use in visuals and such. 

[vkomarag]

@Greg_Deckler

First of all, I cant create 2 columns. There itself i am getting circular depndency. That's the reason i create measures for those 2 columns.

[v-ljerr-msft]

Hi @vkomarag,

Could you post your real table structures(including all the formulas you're using) with some sample data and your expected result? So that we can better assist on this issue. 

It's better that you can also share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[vkomarag]

Hi @v-ljerr-msft

Data is already provided in the very first notification.

Qfactor=DIVIDE((COUNTX(FILTER('Sheet1','Sheet1'[Rating]=4||'Sheet1'[Rating]=5),'Sheet1'[Rating]) - COUNTX(FILTER('Sheet1','Sheet1'[Rating]=2||'Sheet1'[Rating]=1),'Sheet1'[Rating])),COUNTX('Sheet1','Sheet1'[Rating]))  --Measure

PREV_Qfactor=CALCULATE([QMeasure],FILTER(Sheet1,Sheet1[year]="2015"))  --- Measure

CURR_Qfactor=CALCULATE([QMeasure],FILTER(Sheet1,Sheet1[year]="2016")) --Measure

YTD_Indicator=IF(CURR_Qfactor>PREV_Qfactor,"Up",IF(CURR_Qfactor<PREV_Qfactor,"Down"))  -- This should be a column.Here i am getting circular dependency.

I cannot create as a measure because i need to relate this indicator column to another source Indicator

Indicator source looks like below

Ind_Name  Image url

Up                 <<<<url showing up arrow>>

Down            <<<<url showing down arrow>>

If i create the YTD_Indicator as a measure . A measure cannot be create in a relationship.

Hope you understood my problem.

Let me know if any workaround..Finally i need to show up image instead of Up and Down.

Thanks

KVB

[v-ljerr-msft · Accepted solution]

Hi @vkomarag,

If I understand you correctly, you should be able to follow steps below to get your expected result.

1. Use the formula below to create a new calculate column in your table.

Qfactor = 
VAR currentYear = Sheet1[year]
RETURN
    DIVIDE (
        (
            COUNTX (
                FILTER (
                    ALL ( 'Sheet1' ),
                    ( 'Sheet1'[Rating] = 4
                        || 'Sheet1'[Rating] = 5 )
                        && Sheet1[year] = currentYear
                ),
                'Sheet1'[Rating]
            )
                - COUNTX (
                    FILTER (
                        ALL ( 'Sheet1' ),
                        ( 'Sheet1'[Rating] = 2
                            || 'Sheet1'[Rating] = 1 )
                            && Sheet1[year] = currentYear
                    ),
                    'Sheet1'[Rating]
                )
        ),
        COUNTX (
            FILTER ( ALL ( 'Sheet1' ), Sheet1[year] = currentYear ),
            'Sheet1'[Rating]
        )
    )

2. Then you should be able to use the formula below to create a new calculate column for YTD_Indicator. 

YTD_Indicator = 
VAR PREV_Qfactor =
    CALCULATE (
        MAX ( Sheet1[Qfactor] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[year] = EARLIER ( Sheet1[year] ) - 1 )
    )
VAR CURR_Qfactor = Sheet1[Qfactor]
RETURN
    IF (
        CURR_Qfactor > PREV_Qfactor,
        "Up",
        IF ( CURR_Qfactor < PREV_Qfactor, "Down" )
    )

Regards

[vkomarag]

HI @v-ljerr-msft

I have ended up in measure showing the statc value for any question grouped. Any pointers?

[vkomarag]

@v-ljerr-msft

I have tried the given one.It worked. About to create another columns or measures based on this Qfactor.

Hopefully it should work. 

Thanks again for your time and effort.

[vkomarag]

CURR_Qfactor should be the recent year and calculate similar to PREV_Qfactor.I can modify the formula.Not an issue.Let me try and get back to you.

[Greg_Deckler]

OK, then you're probably going to have to learn how to do it in M code then so that when it gets into the model, you already have the column that you need to perform the link on. However, that may in and of itself prove problematic. Going to need to see all of the formulas and such for things like QFACTOR, etc.