Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic Chart based on Variable field

Hello,

 

As a Qlikview developer I find myself facing various difficulties with Power BI because I lost all my bearings.

 

Maybe my method is not the good one so please, let me know the best practice...

 

I created 2 tables by entering data :

PERIOD : Period

KPI : KPI

In the Period table I have the values MTD, QTD, YTD

In the KPI table I have different KPIs : IMC, IMG, Sales...

 

Then I have 2 slices, one for the period and the other with KPIs.

 

And finally, I would like to make a pie chart with in values the concatenation ok KPI and period which is an amount existing in a FACT table, like "IMC YTD" or "IMG QTD"... depending on what the user has selected.

 

So I tried to make a measure like this :

KPI Variable = 
var KPISelected = SELECTEDVALUE('KPI'[KPI])
var PeriodSelected = SELECTEDVALUE(PERIOD[Period])
return CALCULATE(KPISelected &" " & PeriodSelected)

But it juste returns the name of the field "IMC YTD", not the value...

How can I make the formula interpret the variable as a field and not as a text???

 

Thanks for help

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Assume your fact table as below

    add columns in the pie chart as above, go to "Format" pane, turn on "details label", select "Data value" for "Label style", in this way it show amount values instead of name of the field "IMC YTD".

     

    create measures in fact table

    Measure = SELECTEDVALUE('KPI'[KPI])&" "&SELECTEDVALUE(Period[Period])

Measure 2 = IF([Measure]=MAX('FACT'[Merged]),1,0)

     

    remove all columns from the table visual but keep "merged" column

    then add [Measure2] in the visual level filter in the table visual, select "show items when value is 1"

    click on the cell on the table visual

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.