Graph output not showing Correct

[sakshikaul]

HI,

I want in the following graph when I select year as 2021(current year) then it should only show values of all the months of current year ie Jan and feb for 2021.

And automatically previous year (2020 all the months ie Jan -Dec should be shown in graph) on selection of 2021(current Year)

But in my following graph data for 2021 is showing me all the months( Jan -Dec( whereas it should only show jan , feb) and 2020 also data is captured for all the months . Please help

Year selected=2021

Following is the DAX written in Power BI

MaxYear1_HAWB = If(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",CALCULATE(SUM(DBALLSTAT[Profit])/1000,FILTER((DBALLSTAT),DBALLSTAT[Eco_year]=MAX(DBALLSTAT[Eco_year])),FILTER(Group_Logic,Group_Logic[Load Type]="HAWB")))

whereas I am basically migrating qlikview application to power BI so in qlikview the expression is written as 

=if(substringcount(Concat(distinct '~' & [Load Type] & '~' ,'|'),'~HAWB')=1,

SUM({<Eco_year = {'$(Maxyear)'},[Load Type]={'HAWB'}>}(Profit)/1000)

,'')

Thanks in advance

[v-kkf-msft]

Hi @sakshikaul ,

I added a condition about the date ie DBALLSTAT[Date] to the formula, you try to see if it can get the result you want. If it is unsuccessful, please provide some sample data and relationships between tables.  

MaxYear1_HAWB = 
If(
  SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
  CALCULATE(
    SUM(DBALLSTAT[Profit])/1000,
    FILTER(
      (DBALLSTAT),
      DBALLSTAT[Eco_year]=MAX(DBALLSTAT[Eco_year])
      && DBALLSTAT[Date] <= EOMONTH(TODAY(),-1)
    ),
    FILTER(
      Group_Logic,
      Group_Logic[Load Type]="HAWB"
    )
  )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sakshikaul]

Hi,

Earlier on selecting 2021 filter the data was not getting filtered for 2021. It was showing all the months for 2021 ie jan to dec and same for 2020

but now I have solved one issue like now on selecting year =2021 I am getting data for jan and feb only which is correct but for 2020 I am not getting values. please help 

Expression written for current Year

Maxyear1_hawb=

var summaryA=MAX(DBALLSTAT[Eco_year]) var summaryB=If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
CALCULATE(
SUM(DBALLSTAT[Profit])/1000,
FILTER(
(DBALLSTAT),
DBALLSTAT[Eco_year]=summaryA
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"
)
))
return summaryB

expression for prev year

PrevYear1_HAWB = var Summary_hawb_prev=MAX(DBALLSTAT[Eco_year])-1 var summary_hawb_prevB= If(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",CALCULATE(SUM(DBALLSTAT[Profit])/1000,FILTER((DBALLSTAT),DBALLSTAT[Eco_year]=Summary_hawb_prev),FILTER(ALL(Group_Logic),Group_Logic[Load Type] ="HAWB"))) return Summary_hawb_prev

[v-kkf-msft]

Hi @sakshikaul ,

Modify the measure PrevYear1_HAWB:

PrevYear1_HAWB = 
var Summary_hawb_prev = MAXX(ALL(DBALLSTAT),DBALLSTAT[Eco_year])-1 
var summary_hawb_prevB = 
  If(
    SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
    CALCULATE(
      SUM(DBALLSTAT[Profit])/1000,
      FILTER(
        (DBALLSTAT),
        DBALLSTAT[Eco_year] = Summary_hawb_prev
      ),
      FILTER(
        ALL(Group_Logic),
        Group_Logic[Load Type] = "HAWB"
      )
    ) 
  ) 
return Summary_hawb_prev

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sakshikaul]

HI,

If I am not selecting any year then I am getting the correct result but if I am selecting any year then I am not getting the result...I want on selection of year and non selection of year (both) I want values for current year and previous year. 

for eg- if I selected year=2018 then the values for previous year should also reflect like 2018 and 2017( previous year)

and if I am not selecting any year then values of max (year) and max(year)-1 should be reflected in graph( this is working fine now)

On selection of year=2021 not getting the desired result ie values for 2020 should also be reflected in graph

If I not selecting any year getting correct result

[v-kkf-msft]

Hi @sakshikaul ,

The Year field you put in the slicer should come from a separate table. Because if you use DBALLSTAT[Eco_year], then you choose 2021, only the 2021 data will be filtered out.

You can refer to the following sample data for modification.

Measure = 
var MaxYear = MAXX(ALL('Table'),'Table'[year])
var SelectYear = 
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            'Table',
            'Table'[year] in { ALLSELECTED(Slicer[Year]), ALLSELECTED(Slicer[Year])-1 }
        )
    )
var NotSelect = 
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            'Table',
            'Table'[year] in { MaxYear, MaxYear-1 }
        )
    )
return 
    IF(ISFILTERED(Slicer),SelectYear,NotSelect)

     

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sakshikaul]

Hi,

You want me to create duplicate of DBALLSTAT[Eco_year] for slicer?

and also,

if I am suppose to create duplicate of dballstat(eco_year)  for slicer with the table name ecoYeartable

then,
Measure =
var MaxYear = MAX(ALL('ecoYeartable'),'ecoYeartable'[year])
var SelectYear =
CALCULATE(
SUM('Table'[value]),----> which value ?? 

CALCULATE(
SUM(DBALLSTAT[Profit])/1000, FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",
FILTER(
'Table',
'Table'[year] in { ALLSELECTED(Slicer[Year]), ALLSELECTED(Slicer[Year])-1 }
)
)
var NotSelect =
('CALCULATE(
SUM(DBALLSTAT[Profit])/1000,),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",

FILTER(
'Table',
'Table'[year] in { MaxYear, MaxYear-1 }
)
)
return
IF(ISFILTERED(Slicer),SelectYear,NotSelect)

THIS IS HOW THE EXPRESSION WILL BE WRITTEN ?

[sakshikaul]

Hi,

below measure is also giving me an error...please help?

Measure =
var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
var SelectYear =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
CALCULATE(
SUM(divide(DBALLSTAT[Profit]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",
SUM(DBALLSTAT[CBM]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",
)
)
)
var NotSelect =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
CALCULATE(
SUM(DBALLSTAT[Profit])/1000,
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 }
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"
)
)
)
return
IF(ISFILTERED(Slicer),SelectYear,NotSelect)

[sakshikaul]

I am not suppose to give a seperate calender in UI design. I want on selection of any Year... data for that selected year and corresponding previous year should get reflected in chart ie if current year selected= 2020 then current year and previous year =2019 data should be reflected in the chart 

and by default if nothing is selected then data for max(year) and max(year)-1 should be selected...

[v-kkf-msft]

Hi @sakshikaul ,

I understand what you think. But in desktop, the slicer will filter the data in the same table.

That is, if you use DBALLSTAT[Eco_year] as the value of the slicer, when you select 2021, the desktop will first filter out the rows with the value of 2021 in the column Eco_year in the DBALLSTAT table. The measures you create will be calculated based on the filtered rows, which is why the data in 2020 will not be displayed when you choose 2021.

[v-kkf-msft]

Hi @sakshikaul ,

Try this formula:

Measure = 
var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
var SelectYear = 
    If(
        SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
        CALCULATE(
            SUM(DBALLSTAT[Profit])/1000,
            FILTER(
               DBALLSTAT,
               DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1}
            ),
            FILTER(
                Group_Logic,
                Group_Logic[Load Type]="HAWB"
            )
        )
    )
var NotSelect = 
    If(
        SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
        CALCULATE(
            SUM(DBALLSTAT[Profit])/1000,
            FILTER(
               DBALLSTAT,
               DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 }
            ),
            FILTER(
                Group_Logic,
                Group_Logic[Load Type]="HAWB"
            )
        )
    )
return 
    IF(ISFILTERED(Slicer),SelectYear,NotSelect)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[sakshikaul]

Still not getting the output.. getting following above  error 

[v-kkf-msft]

Hi @sakshikaul ,

Change the type of column Slicer[Year] to the same type as column DBALLSTAT[Eco_year].

[sakshikaul]

so for this can I simply duplicate the column in same table ?

[v-kkf-msft]

Hi @sakshikaul ,

Yes, you can quickly select a column to copy in the Query Editor, and then put it in the Slicer table. It should be noted that after you create the Slicer table, make sure that it has no relationship with the DBALLSTAT table, and the Year column in Slice table has the same type as the Eco_year column in DBALLSTAT table.

[sakshikaul]

Like I have created two seperate expressions for previous and current year... so shouuld I write following code in single expression now?

Measure =
var MaxYear = MAXX(ALL('Table'),'Table'[year])
var SelectYear =
CALCULATE(
SUM('Table'[value]),
FILTER(
'Table',
'Table'[year] in { ALLSELECTED(Slicer[Year]), ALLSELECTED(Slicer[Year])-1 }
)
)
var NotSelect =
CALCULATE(
SUM('Table'[value]),
FILTER(
'Table',
'Table'[year] in { MaxYear, MaxYear-1 }
)
)
return
IF(ISFILTERED(Slicer),SelectYear,NotSelect)

I have cretaed two seperate measure for current and previous year... so should I write only one expression now?

[v-kkf-msft]

Hi @sakshikaul ,

I think creating one is enough. like this：

[sakshikaul]

Hi,

I am facing a major issue like if  I am adding more than one measure( ie GP,Volume and nd yeild) in a chart it is getting automatically added in a tooltip and then selecting GP, on axis- month, I am not able to add legend as Eco year in that case,

Please check below image :-

I am able to add only one measure. On adding 2nd measure it is automatically getting added in a tooltip. How to add all the three measures in one chart?

but if trying to add 2nd measure ie for volume then not able to add eco year as legend 

[v-kkf-msft]

Hi @sakshikaul ,

What you mean is that when GP is selected, the output is all normal. However, when Volume or Yield is selected, a null value is output. In this case, I suggest you create another measure according to the following formula, and then put it into the Values pane.

Measure = 
    SWITCH(
        SELECTEDVALUE('Summary Field Selection'[Field Value]),
        "GP",[MaxYear1_HAWB],
        "Volume",[MaxYear2_HAWB],
        "Yield",[MaxYear3_HAWB]
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[sakshikaul]

Hi, 

Now its working fine after adding above mentioned measure but now if I am selecting dballstat(eco_year)=2021...its not giving me previous year output...

But if I am selecting slicer(eco_year)= 2021 then its showing me correct output

but I want there should be only single calender ie dblstat(eco_year)  for selection of the year

[v-kkf-msft]

Hi @sakshikaul ,

Unfortunately, as I said before, if you use dblstat(eco_year) as the value of the slicer, it will only display the data for the year you selected. This cannot be changed by writing DAX.

If you want it to display data for the selected year and the previous year, you must use slicer(eco_year).

I have a question. If you want the line chart to display data for 2 years, which year of data do other visuals display, such as the following.

Best Regards,
Winniz