Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

help with dax calculation on which date column to use

Hi,

 

I need to create a bar chart with values per month.

 

I have 2 date columns to use - one for registration date, another for generation date of code. 

I also have some KPIs calculated for values.

 

What I need is that when KPI 1 or 2 or 3 is selected, the bar chart should use registration date in the axis, when the other KPIs are selected the bar chart should use generation date of the code.

 

Can you please help?

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

     

    What you want to achieve is a dynamic axis.
    So you can change the axis by slicer to show different KPI indicators.

     

    Here is an example I created where I can change the axis by selecting the value of the slicer.

    What you need to do is to aggregate the different KPIs into a new calculation table and then use the TREATAS function to filter the table to get the results.

    _KPI = 
VAR table1=SELECTCOLUMNS(ADDCOLUMNS(VALUES('Product'[Name]),"KPI","Product"),"KPI",[KPI],"category",[Name])
VAR table2=SELECTCOLUMNS(ADDCOLUMNS(VALUES('Client'[City]),"KPI","City"),"KPI",[KPI],"category",[City])
RETURN UNION(table1,table2)
    value = 
    SWITCH(TRUE(),
        SELECTEDVALUE('_KPI'[KPI])="Product",CALCULATE(_KPI[_Sales],TREATAS(VALUES('_KPI'[category]),'Product'[Name])),
        SELECTEDVALUE('_KPI'[KPI])="City",CALCULATE(_KPI[_Sales],TREATAS(VALUES('_KPI'[category]),Client[City]))
    )

     

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    What you want to achieve is a dynamic axis.
    So you can change the axis by slicer to show different KPI indicators.

     

    Here is an example I created where I can change the axis by selecting the value of the slicer.

    What you need to do is to aggregate the different KPIs into a new calculation table and then use the TREATAS function to filter the table to get the results.

    _KPI = 
VAR table1=SELECTCOLUMNS(ADDCOLUMNS(VALUES('Product'[Name]),"KPI","Product"),"KPI",[KPI],"category",[Name])
VAR table2=SELECTCOLUMNS(ADDCOLUMNS(VALUES('Client'[City]),"KPI","City"),"KPI",[KPI],"category",[City])
RETURN UNION(table1,table2)
    value = 
    SWITCH(TRUE(),
        SELECTEDVALUE('_KPI'[KPI])="Product",CALCULATE(_KPI[_Sales],TREATAS(VALUES('_KPI'[category]),'Product'[Name])),
        SELECTEDVALUE('_KPI'[KPI])="City",CALCULATE(_KPI[_Sales],TREATAS(VALUES('_KPI'[category]),Client[City]))
    )

     

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This is exactly what I need. Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amitchandak,

       

      I don't see the answer on that topic.

       

      My issue is that I need to change the axis source based on the selection. If my selection is A, I want Date1 on the x axis. If my selection is B, I want Date2 on the x axis.

       

      Thanks!