Search code examples
powerbidaxpowerquerym

PowerBI Need Help to get resolve on multiple queries of Custom KPI creation

I am working on power-bi and writing dax queries from last 2 months, and trying to create custom kip's like showing UP and Down arrows for different kind of data.

But facing multiple issues.

I need your valuable suggestions to resolve these issues.

Sample Data:-

enter image description here

Q1:- Cant Show Images of UP and Down arrows while creating Custom KPI.

Measure for KPI creations are,

PlanPrevMon = CALCULATE([PlanSum],PREVIOUSMONTH('Month Year'[Date]))

Measure = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))

And this gives me perfect result(please suggest me if any better way for this).

But when i tried to show UP and DOWN image arrows instead of uni-char arrows but it is not working.

Measure is:

Measure = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png","https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png")&IF([PlanSum]<=0,"",""))

Output is:-

enter image description here

I think here the image url it is taking it as a string so that why i am unable to get the image

How do i can get the Image arrow.Or else is there any other way to do this?

Q2:- Same queries But not working in percentage values.

Table

enter image description here

For Calculating the previews month values

Measure:-

Contri Prev Mon = CALCULATE([Contri%],PREVIOUSMONTH('Month Year'[Date]))

But the values are changed to decimal like as in below image.

enter image description here

Why am i getting the values in decimals here. If i changed that Contri% to decimals and do the rest of queries like

ContriArrows = IF(ISBLANK([Contri%]),"No Data Available ",[Contri%])&" "&IF([Contri%]=[Contri Prev Mon],"",IF([Contri%] > [Contri Prev Mon],UNICHAR(8679),UNICHAR(8681))&IF([Contri%]<=0,"",""))

And the output is

enter image description here

Fine its perfect.

but same as 1st question, if i include the UP and Down arrows images URL's it gives same output as q1(exact output as shown in 2nd image)

So how can i show the values in % and with the Up and Down arrows?

Q3:- And for this contri% arrows kpi i have written another calculated column,

ContriKeys1 = SWITCH(
        TRUE(),
            [Contri Prev Mon]=BLANK(),"",
            [Contri%] <= 0,"",
            [Contri%] > [Contri Prev Mon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png",
            [Contri%] < [Contri Prev Mon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png",
            [Contri%] = [Contri Prev Mon],"")

But it giving me the wrong arrow symbol as in below image.

enter image description here

Why am i getting those wrong arrow symbols here?

Any suggestions will be appreciable.

Thanks,

Bunny.

Solution

  • This might help: I was able to get this...

    enter image description here

    If PlanSum is more than PlanPrevMon, the Icon arrow points up. If PlanSum is less than PlanPrevMon, the Icon arrow points down.

    The LastIcon arrow is just, as the name suggests, what the last Icon arrow is. (I only included it for insight...I know you wouldn't actually want to show it like that.)

    The arrow on the card is also the LastIcon.

    Here's how I did it (picking up from my earlier answer to you):

    1. I added a column named Icon.

      Icon = if([PlanSum]>Sheet1[PlanPrevMon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png",if(Sheet1[PlanSum]<Sheet1[PlanPrevMon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png",""))

    2. I added a column named Last Icon.

      LastIcon = calculate(lastnonblank(Sheet1[Icon],1),FILTER(Sheet1,Sheet1[id]=max(Sheet1[id])))

    3. I changed the Data Category to Image URL. <= This is important! enter image description here

    4. I added the Icon and LastIcon columns to the table visual.

    5. I added another table visual with just the LastIcon in it.

      • Then I formatted that table to hide the column title and that title's underline.
      • Then I moved that table over the card from earlier.